Re: LTRIM bug?

From: Anton Buijs <remove_aammbuijs_at_xs4all.nl>
Date: Fri, 8 Aug 2003 21:42:18 +0200
Message-ID: <3f33fd24$0$49098$e4fe514c@news.xs4all.nl>

Ed Wong <ewong_at_mail.com> schreef in berichtnieuws a5ae1554.0308081116.46fe84ee_at_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:
<quote>
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
</quote>

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 Received on Fri Aug 08 2003 - 14:42:18 CDT