I received this email today:

Hi Steven When executing the following query I receive an output with the first letter missing. Is this an Oracle bug? select LTRIM('<br />believe it or not','<br />') from dual; Thanks, Marcel

First of all, I need to point out to Marcel and perhaps many other PL/SQL developers: you do not need to call LTRIM (or SYSDATE or USER or....) within a SELECT from dual. Instead you can run this SQL function directly inside PL/SQL, as in:

BEGIN
   DBMS_OUTPUT.put_line (LTRIM ('<br />believe it or not', '<br />'));
END;

If you do, you will see this output:

elieve it or not

Marcel feels that this is the wrong result, but in fact it is perfectly correct. You see, LTRIM does not trim by patterns of characters. It trims any and all of the individual characters you provide in the list. So what Marcel requested in the above code can be translated into English as follows:

"Please trim < and b and r and / and > from the beginning of this string."

Thus, the following block will display the same results:

BEGIN
   DBMS_OUTPUT.put_line (
      LTRIM ('<<>>brrrrrrr/////<br />believe it or not', '<br />'));
END;

I assume what Marcel wanted to do was remove
from the start of the string only if it is present. To do that, you might write something like this:

CREATE OR REPLACE FUNCTION lremove (string_in         IN VARCHAR2
                                  , remove_in         IN VARCHAR2
                                  , case_sensitive_in IN BOOLEAN DEFAULT TRUE
                                   )
   RETURN VARCHAR2
IS
BEGIN
   RETURN CASE CASE
                  WHEN case_sensitive_in THEN INSTR (string_in, remove_in)
                  ELSE INSTR (UPPER (string_in), UPPER (remove_in))
               END
             WHEN 1
             THEN
                SUBSTR (string_in, LENGTH (remove_in) + 1)
             ELSE
                string_in
          END;
END lremove;
/

BEGIN
   DBMS_OUTPUT.put_line (lremove ('<br />believe it or not', '<br />'));
   DBMS_OUTPUT.put_line (lremove ('Whether you believe it or not', '
'));
END;
/

believe it or not
Whether you believe it or not