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 with 6 Comments | Tags: function, sql, trim

---