msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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.


Filed in Oracle on 28 Aug 08 | Tags: , ,


Reader's Comments

  1. |

    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
    
  2. |

    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.

  3. |

    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).

  4. |

    Hmm! What is the difference between trim(sysdate) and to_char(sysdate)?

  5. |

    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

  6. |

    Thanks Brian. By the way, nice use of the DUMP function.