Styjun

I'm half of a hundred

When and why did the House rules change to permit an inquiry without a vote?

How much does freezing grapes longer sweeten them more?

Is it really better for the environment if I take the stairs as opposed to a lift?

Did I Traumatize My Puppy?

In this day and age should the definition / categorisation of erotica be revised?

Who inspired the character Geordi La Forge?

Did the US push the Kurds to lower their defences against Turkey in the months preceding the latest Turkish military operation against them?

How can I seal 8 inch round holes in my siding?

Why were germanium diodes so fast and germanium transistors so slow?

What is the maximum amount of melee weapon attacks a character can make reliably every turn?

when to use がつ or げつ readings for 月？

Use GPLv3 library in a closed system (no software distribution)

What is the white square near the viewfinder of the Fujica GW690?

I don't want my ls command in my script to print results on screen

What would a chair for a Human with a Tail look like?

Can I use Oko's ability targetting a creature with protection from green?

Will I be allowed to enter the US after living there illegally then legally in the past?

How does an Evocation Wizard's Overchannel ability interact with Chaos Bolt?

Can set-like objects obeying ZFC be constructed in Euclidean geometry?

Would a spacecraft carry arc welding supplies?

Is the phrase “You are requested” polite or rude?

Stare long enough and you will have found the answer

When applying for a visa has there ever been a case of embassy asking for proof of right to be in the present country?

Why the LTRIM function is not returning the expected result

How to return only the Date from a SQL Server DateTime datatypeSQL select join: is it possible to prefix all columns as 'prefix.*'?How do I limit the number of rows returned by an Oracle query after ordering?Insert results of a stored procedure into a temporary tableSQL Server query - Selecting COUNT(*) with DISTINCTMySQL select where column is not emptySQL Server: How to Join to first rowsql - single query to return values that are not presentreturn null if no rows found oracle query with IN clauseCross join returning row from only one table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

2

I am trying the get the location name after the title from a column. An example of a position name is 'Manager - Miami'. I am trying the following,

 SELECT UPPER(LTRIM('Manager - Miami', 'Manager - ')) FROM DUAL

I am expecting the output to be MIAMI, but it's returning only IAMI.

sql oracle plsql

share|improve this question

edited Mar 29 at 20:00

Priom

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

add a comment
 | 

2

I am trying the get the location name after the title from a column. An example of a position name is 'Manager - Miami'. I am trying the following,

 SELECT UPPER(LTRIM('Manager - Miami', 'Manager - ')) FROM DUAL

I am expecting the output to be MIAMI, but it's returning only IAMI.

sql oracle plsql

share|improve this question

edited Mar 29 at 20:00

Priom

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

add a comment
 | 

I am trying the get the location name after the title from a column. An example of a position name is 'Manager - Miami'. I am trying the following,

 SELECT UPPER(LTRIM('Manager - Miami', 'Manager - ')) FROM DUAL

I am expecting the output to be MIAMI, but it's returning only IAMI.

sql oracle plsql

share|improve this question

edited Mar 29 at 20:00

Priom

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

 SELECT UPPER(LTRIM('Manager - Miami', 'Manager - ')) FROM DUAL

share|improve this question

edited Mar 29 at 20:00

Priom

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

share|improve this question

edited Mar 29 at 20:00

Priom

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

asked Mar 28 at 21:33

PriomPriom

3377 bronze badges

3 Answers
3

active

oldest

votes

2

(L)TRIM? Why would you use it to return "Miami"? I'd go with SUBSTR + INSTR, such as in this example (CTE is just so that I wouldn't have to repeat the string several times).

SQL> with test (col) as
 2 (select 'Manager - Miami' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

RESUL
-----
Miami

SQL>

Besides, what is District Manager - 's purpose? Do you want to select location from that too? There's none, so - NULL will be returned:

SQL> with test (col) as
 2 (select 'District Manager - ' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

R
-


SQL>

share|improve this answer

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks. District Manager was written by mistake, it should be only 'Manager'. I've edited the question. This will work for my case.
  
  – Priom
  Mar 28 at 21:52
  

  
  
  
  

add a comment
 | 

3

In Oracle, the second argument to LTRIM() is a list of characters. By default, these are case-sensitive.

So, 'm' is the first character in ''Manager - Miami' that is not in 'District Manager - ', so everything before it is removed.

I can speculate that you actually want some sort of regexp_substr() or regexp_replace(), but you don't really specify the logic.

share|improve this answer

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the explanation. It was removing all the characters from the 'set'. A combination of SUBSTR and INSTR worked perfectly.
  
  – Priom
  Mar 28 at 21:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Priom . . . If you had explained what you wanted to accomplish, I probably would have answered that as well.
  
  – Gordon Linoff
  Mar 28 at 22:12
  

  
  
  
  

add a comment
 | 

2

And here's the regex_substr version that will select what is after the last dash/space at the end of the string:

SELECT REGEXP_SUBSTR('Manager - Miami', '- (.*)$', 1, 1, NULL, 1) FROM DUAL;

share|improve this answer

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55407171%2fwhy-the-ltrim-function-is-not-returning-the-expected-result%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

(L)TRIM? Why would you use it to return "Miami"? I'd go with SUBSTR + INSTR, such as in this example (CTE is just so that I wouldn't have to repeat the string several times).

SQL> with test (col) as
 2 (select 'Manager - Miami' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

RESUL
-----
Miami

SQL>

Besides, what is District Manager - 's purpose? Do you want to select location from that too? There's none, so - NULL will be returned:

SQL> with test (col) as
 2 (select 'District Manager - ' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

R
-


SQL>

share|improve this answer

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks. District Manager was written by mistake, it should be only 'Manager'. I've edited the question. This will work for my case.
  
  – Priom
  Mar 28 at 21:52
  

  
  
  
  

add a comment
 | 

2

(L)TRIM? Why would you use it to return "Miami"? I'd go with SUBSTR + INSTR, such as in this example (CTE is just so that I wouldn't have to repeat the string several times).

SQL> with test (col) as
 2 (select 'Manager - Miami' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

RESUL
-----
Miami

SQL>

Besides, what is District Manager - 's purpose? Do you want to select location from that too? There's none, so - NULL will be returned:

SQL> with test (col) as
 2 (select 'District Manager - ' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

R
-


SQL>

share|improve this answer

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks. District Manager was written by mistake, it should be only 'Manager'. I've edited the question. This will work for my case.
  
  – Priom
  Mar 28 at 21:52
  

  
  
  
  

add a comment
 | 

(L)TRIM? Why would you use it to return "Miami"? I'd go with SUBSTR + INSTR, such as in this example (CTE is just so that I wouldn't have to repeat the string several times).

SQL> with test (col) as
 2 (select 'Manager - Miami' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

RESUL
-----
Miami

SQL>

Besides, what is District Manager - 's purpose? Do you want to select location from that too? There's none, so - NULL will be returned:

SQL> with test (col) as
 2 (select 'District Manager - ' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

R
-


SQL>

share|improve this answer

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

SQL> with test (col) as
 2 (select 'Manager - Miami' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

RESUL
-----
Miami

SQL>

SQL> with test (col) as
 2 (select 'District Manager - ' from dual)
 3 select trim(substr(col, instr(col, '-') + 1)) result
 4 from test;

R
-


SQL>

share|improve this answer

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

answered Mar 28 at 21:39

LittlefootLittlefoot

35.5k88 gold badges1717 silver badges3737 bronze badges

3

In Oracle, the second argument to LTRIM() is a list of characters. By default, these are case-sensitive.

So, 'm' is the first character in ''Manager - Miami' that is not in 'District Manager - ', so everything before it is removed.

I can speculate that you actually want some sort of regexp_substr() or regexp_replace(), but you don't really specify the logic.

share|improve this answer

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the explanation. It was removing all the characters from the 'set'. A combination of SUBSTR and INSTR worked perfectly.
  
  – Priom
  Mar 28 at 21:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Priom . . . If you had explained what you wanted to accomplish, I probably would have answered that as well.
  
  – Gordon Linoff
  Mar 28 at 22:12
  

  
  
  
  

add a comment
 | 

3

In Oracle, the second argument to LTRIM() is a list of characters. By default, these are case-sensitive.

So, 'm' is the first character in ''Manager - Miami' that is not in 'District Manager - ', so everything before it is removed.

I can speculate that you actually want some sort of regexp_substr() or regexp_replace(), but you don't really specify the logic.

share|improve this answer

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for the explanation. It was removing all the characters from the 'set'. A combination of SUBSTR and INSTR worked perfectly.
  
  – Priom
  Mar 28 at 21:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Priom . . . If you had explained what you wanted to accomplish, I probably would have answered that as well.
  
  – Gordon Linoff
  Mar 28 at 22:12
  

  
  
  
  

add a comment
 | 

In Oracle, the second argument to LTRIM() is a list of characters. By default, these are case-sensitive.

So, 'm' is the first character in ''Manager - Miami' that is not in 'District Manager - ', so everything before it is removed.

I can speculate that you actually want some sort of regexp_substr() or regexp_replace(), but you don't really specify the logic.

share|improve this answer

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

share|improve this answer

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

answered Mar 28 at 21:37

Gordon LinoffGordon Linoff

866k3838 gold badges360360 silver badges459459 bronze badges

2

And here's the regex_substr version that will select what is after the last dash/space at the end of the string:

SELECT REGEXP_SUBSTR('Manager - Miami', '- (.*)$', 1, 1, NULL, 1) FROM DUAL;

share|improve this answer

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges

add a comment
 | 

2

And here's the regex_substr version that will select what is after the last dash/space at the end of the string:

SELECT REGEXP_SUBSTR('Manager - Miami', '- (.*)$', 1, 1, NULL, 1) FROM DUAL;

share|improve this answer

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges

add a comment
 | 

And here's the regex_substr version that will select what is after the last dash/space at the end of the string:

SELECT REGEXP_SUBSTR('Manager - Miami', '- (.*)$', 1, 1, NULL, 1) FROM DUAL;

share|improve this answer

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges

SELECT REGEXP_SUBSTR('Manager - Miami', '- (.*)$', 1, 1, NULL, 1) FROM DUAL;

share|improve this answer

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges

answered Mar 28 at 21:44

Gary_WGary_W

7,50311 gold badge1313 silver badges2929 bronze badges