SQL Function Spotlight: TRIM < Eddie Awad’s Blog

SQL Function Spotlight: TRIM

The SQL function TRIM has been around since Oracle 8i and maybe earlier. TRIM enables you to trim characters from a character string. The following examples demonstrate its usage and show you a few little known features of this simple function.

Remove leading and trailing blank spaces:

SQL> SELECT TRIM ('  mystring  ') FROM dual;

TRIM('MY
--------
mystring

Remove any leading characters equal to ‘x’:

SQL> SELECT TRIM (LEADING 'x' FROM 'xxmystringxx') FROM dual;

TRIM(LEADI
----------
mystringxx

Remove any trailing characters equal to ‘x’:

SQL> SELECT TRIM (TRAILING 'x' FROM 'xxmystringxx') FROM dual;

TRIM(TRAIL
----------
xxmystring

Removes leading and trailing characters equal to ‘x’:

SQL> SELECT TRIM (BOTH 'x' FROM 'xxmystringxx') FROM dual;

TRIM(BOT
--------
mystring

Removes leading and trailing characters equal to ‘x’ (Same as BOTH):

SQL> SELECT TRIM ('x' FROM 'xxmystringxx') FROM dual;

TRIM('X'
--------
mystring

My usage of this function has mostly been to trim blanks from both ends of a string. How about you?

Related functions: RTRIM and LTRIM.

Tagged function, sql, trim | Comments Closed | Trackbacks Closed

Comments (6)

Laurent Schneider wrote:
there is one major advantage of using ltrim (rtrim), with ltrim you can skip any character from a range, with trim the character is a single character !
```
SQL> select ltrim('elcaroracle','racle') from dual

LTRIM(
------
oracle
```
August 28th, 2008, at 11:35 pm #
Eddie Awad wrote:
Indeed, if you want to trim more than one character on either or both ends of a string, TRIM is not the function to use.
August 29th, 2008, at 10:27 am #
Mathias Magnusson wrote:
I like using trim on dates. I have seen many elaborate ways of converting to char to remove it and then convert back to date. Using trim seems to be the slick way to just remove the time (making it zero).
September 4th, 2008, at 1:25 pm #
Eddie Awad wrote:
Hmm! What is the difference between trim(sysdate) and to_char(sysdate)?
September 4th, 2008, at 5:27 pm #
Brian Tkatch wrote:
So, TRIM and TO_CHAR seem the same by a DATE:

SELECT TRIM(SysDate), DUMP(TRIM(SysDate)) FROM Dual UNION ALL
SELECT TO_CHAR(SysDate), DUMP(TO_CHAR(SysDate)) FROM Dual;

05-SEP-08 Typ=1 Len=9: 48,53,45,83,69,80,45,48,56
05-SEP-08 Typ=1 Len=9: 48,53,45,83,69,80,45,48,56

TRUNC, however, leaves it as a DATE, which is possible the best way to remove the time.

SELECT SysDate, DUMP(SysDate) FROM Dual UNION ALL
SELECT TRUNC(SysDate), DUMP(TRUNC(SysDate)) FROM Dual;

05-SEP-08 Typ=13 Len=8: 216,7,9,5,8,51,44,0
05-SEP-08 Typ=13 Len=8: 216,7,9,5,0,0,0,0
September 5th, 2008, at 5:52 am #
Eddie Awad wrote:
Thanks Brian. By the way, nice use of the DUMP function.
September 7th, 2008, at 6:42 pm #