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.