Home » SQL & PL/SQL » SQL & PL/SQL » LTRIM mystery
LTRIM mystery [message #209583] Fri, 15 December 2006 12:47 Go to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Hello gurus.

I need to remove all salutations from a dataset that is being produced by a fairly involved script that I'm running. Most of the salutation prefixes are in the appropriate field and are easily being suppressed but there are a few records for which the term 'The Honorable' is a part of the first name field so I figured I'd used the LTRIM function to accomplish that suppression. This worked without a problem except that I found that some characters were being removed from records that seemingly shouldn't have been affected. I tested this issue outside of the script and it still trimmed the characters. Any way of getting it to stop doing this? Here is my example test:

SQL> select ltrim('H. Ross Perot, Jr.', 'The Honorable ') from dual;

LTRIM('H.ROSSPERO
-----------------
. Ross Perot, Jr.

The H is being suppressed. Is there a logical reason for this?

Thanks in advance for your help.


Re: LTRIM mystery [message #209585 is a reply to message #209583] Fri, 15 December 2006 13:10 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
LTRIM will remove ANY of the characters that are present in the string 'The Honorable' from the beginning of your string. Therefore, since H is in the string Honorable, the 'H' gets removed from 'H. Ross Perot, Jr.'.

Another example is:

SQL> select ltrim('oH. Ross Perot, Jr.', 'The Honorable') from dual;

LTRIM('OH.ROSSPER
-----------------
. Ross Perot, Jr.


You could set up your query to find records that have 'The Honorable' as it's first thirteen characters.

If you are on 10g, you can consider a regular expression.
Re: LTRIM mystery [message #209587 is a reply to message #209585] Fri, 15 December 2006 13:20 Go to previous message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Thanks for the response. That is definitely not a good function for this issue then. I'll see if I can attack it in a different way. Thanks again!
Previous Topic: Can I do bulk collect when using database links?
Next Topic: NCLOB to Date - Urgent
Goto Forum:
  


Current Time: Sat Dec 10 10:56:07 CST 2016

Total time taken to generate the page: 0.19731 seconds