I’ve always just used the TRIM-function to remove leading and trailing spaces. While going through Markus Winands presentation “Four* Major Database Release of 2017 in Review” on SlideShare.net, I realized that the TRIM function can do more than just remove spaces.

My standard use of TRIM is usually just to remove leading and traling spaces

SQL> SELECT 'x' || TRIM(' hello ') || 'x' str FROM dual;
STR       
----------
xhellox   

TRIM(LEADING …) which I had forgotten about, is the same as LTRIM(…)

SQL> SELECT 'x' || TRIM(LEADING ' ' FROM ' hello ') || 'x' str FROM dual;
STR       
----------
xhello x  

SQL> SELECT 'x' || LTRIM(' hello ') || 'x' str FROM dual;
STR       
----------
xhello x  

TRIM can also remove other characters than just space; leading, trailing or both:

SQL> SELECT 'x' || TRIM(LEADING '$' FROM ' hello ') || 'x' str FROM dual;
STR       
----------
x hello x 

SQL> SELECT 'x' || TRIM(LEADING '$' FROM '$hello$') || 'x' str FROM dual;
STR       
----------
xhello$x  

SQL> SELECT 'x' || TRIM(LEADING '$' FROM '$$hello$') || 'x' str FROM dual;
STR       
----------
xhello$x  

SQL> SELECT 'x' || TRIM(BOTH '$' FROM '$hel$lo$') || 'x' str FROM dual;
STR       
----------
xhel$lox  

TRIM can only search for one character. Specifying several gives ORA-30001:

SQL> SELECT 'x' || TRIM(LEADING '$#' FROM '$#$hello$') || 'x' STR FROM DUAL;

ORA-30001: trimsettet kan bare inneholde ett tegn

LTRIM/RTRIM can actually remove several characters, but note that the syntax is different (the other way around):

SQL> SELECT 'x' || LTRIM('$#$hello$', '$#') || 'x' STR FROM DUAL;
STR       
----------
xhello$x  

So: If the case is to remove the first character if it is a ‘$’, which is easier to read?

SQL> SELECT SUBSTR('$hello$', 2) str FROM dual;
STR       
----------
hello$    

SQL> SELECT REGEXP_REPLACE('$hello$', '(^\$)') STR FROM DUAL;
STR       
----------
hello$    

SQL> SELECT LTRIM('$hello$', '$') str FROM dual;
STR       
----------
hello$    

SQL> SELECT TRIM(LEADING '$' FROM '$hello$') STR FROM DUAL;
STR       
----------
hello$    

Note that these implementations are not functionally equal although the result is the same in this particular case.

• Substring has an assumption that you want to remove the first character, but does not check what it is.
• LTRIM is shorter to write
• TRIM is more explicit in the syntax, so it’s easier to read, so that would be my preference.
• REGEXP_REPLACE is my least favoured option.