Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news2.euro.net!transit.news.xs4all.nl!not-for-mail Reply-To: "Anton Buijs" From: "Anton Buijs" Newsgroups: comp.databases.oracle.server References: Subject: Re: LTRIM bug? Date: Fri, 8 Aug 2003 21:42:18 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 Lines: 65 Message-ID: <3f33fd24$0$49098$e4fe514c@news.xs4all.nl> NNTP-Posting-Date: 08 Aug 2003 21:42:28 CEST NNTP-Posting-Host: 213.84.255.40 X-Trace: 1060371748 news.xs4all.nl 49098 213.84.255.40:1028 X-Complaints-To: abuse@xs4all.nl Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240122 Ed Wong schreef in berichtnieuws a5ae1554.0308081116.46fe84ee@posting.google.com... | I am using a ltrim function and experiencing a weird result. I am | trying to cut off a string 'COMPANY-' on the left side but I got | different results with different texts (see below). The 2nd query | result is incorrect as the 'C' is missing. It seems to me that if the | first character after trim is the same as the first character before | trims, Oracle cut the first character off the resulting string. | Anyone experiencing this problem? I am on 8.1.7.2.0 EE Sun Solaris | 2.8. | | SQL> select ltrim('COMPANY-JOHN','COMPANY-') from dual; | | LTRI | ---- | JOHN | | SQL> select ltrim('COMPANY-CHARLIE','COMPANY-') from dual; | | LTRIM( | ------ | HARLIE | | Thanks, | ewong It's not a bug, it's like the function behaves. You are misunderstanding the function ltrim (to be honest: I made the same misinterpretation too once). From the manual: Syntax: ltrim(char,set) Purpose LTRIM removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, you must enclose it in single quotes. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result. SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL; LTRIM example ------------ XxyLAST WORD In other words: ltrim removes character by character until it sees a characater that is not listed in the set. Another example to illustrate it better: SQL> select ltrim('xxxyyyzzzLAST','xyz') from dual; LTRI ---- LAST Maybe this is what you want? SQL> select replace('COMPANY-CHARLIE','COMPANY-') from dual; REPLACE ------- CHARLIE