In Oracle, the TRIM() function allows you to trim characters from the sides of a string.

You can trim leading characters, trailing characters, or both.

By default, it trims white space, but you can optionally specify a different character or characters to trim.

Syntax

The syntax goes like this:

TRIM([ { { LEADING | TRAILING | BOTH }
         [ trim_character ]
       | trim_character
       }
       FROM 
     ]
     trim_source
    )

Both trim_character and trim_source can be VARCHAR2 or any data type that can be implicitly converted to VARCHAR2.

Example

Here’s a simple example to demonstrate:

SELECT TRIM('.' FROM '...Cat...')
FROM DUAL;

Result:

   TRIM('.'FROM'...CAT...') 
___________________________ 
Cat                        

In this case, the specified character (.) was removed from both sides of the string.

It was removed from both sides because I didn’t specify which side to remove it from.

We can get the same result by including the BOTH keyword:

SELECT TRIM(BOTH '.' FROM '...Cat...')
FROM DUAL;

Result:

   TRIM(BOTH'.'FROM'...CAT...') 
_______________________________ 
Cat                            

Trim Leading Characters

Here it is again, but with only leading characters removed:

SELECT TRIM(LEADING '.' FROM '...Cat...')
FROM DUAL;

Result:

   TRIM(LEADING'.'FROM'...CAT...') 
__________________________________ 
Cat...                            

Trim Trailing Characters

And here it is with only trailing characters removed:

SELECT TRIM(TRAILING '.' FROM '...Cat...')
FROM DUAL;

Result:

   TRIM(TRAILING'.'FROM'...CAT...') 
___________________________________ 
...Cat                             

Default Character

In this example I don’t specify character to trim, therefore it trims blank space:

SELECT TRIM(' Cat ')
FROM DUAL;

Result:

   TRIM('CAT') 
______________ 
Cat           

It’s not so easy to see the effect when trimming whitespace from both sides.

Here’s another example that makes it easier to see that the white space was removed from both sides:

SELECT 
    'My' || ' Fat ' || 'Cat',
    'My' || TRIM(' Fat ') || 'Cat'
FROM DUAL;

Result:

   'MY'||'FAT'||'CAT'    'MY'||TRIM('FAT')||'CAT' 
_____________________ ___________________________ 
My Fat Cat            MyFatCat                   

Numbers

The string and trim character can be VARCHAR2 or any data type that can be implicitly converted to VARCHAR2, so we can pass a number like the following. However, the return value is VARCHAR2.

SELECT TRIM(LEADING 0 FROM 007)
FROM DUAL;

Result:

   TRIM(LEADING0FROM007) 
________________________ 
7                      

Here it is with a different number being trimmed:

SELECT TRIM(LEADING 1 FROM 117)
FROM DUAL;

Result:

   TRIM(LEADING1FROM117) 
________________________ 
7                        

Null Values

If either the string or the trim character is null the result is null:

SET NULL 'null';

SELECT 
    TRIM(null FROM '...Cat...'),
    TRIM(BOTH FROM null),
    TRIM(null FROM null)
FROM DUAL;

Result:

   TRIM(NULLFROM'...CAT...')    TRIM(BOTHFROMNULL)    TRIM(NULLFROMNULL) 
____________________________ _____________________ _____________________ 
null                         null                  null                 

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Trimming Empty Strings

Passing an empty string as the trim character results in null:

SET NULL 'null';

SELECT TRIM('' FROM '   Cat')
FROM DUAL;

Result:

   TRIM(''FROM'CAT') 
____________________ 
null                

But adding a single space to the empty string changes that, and trims any blank spaces from the string:

SET NULL 'null';

SELECT TRIM(' ' FROM '   Cat')
FROM DUAL;

Result:

   TRIM(''FROM'CAT') 
____________________ 
Cat                 

Incorrect Argument Count

Calling TRIM() without passing any arguments returns an error:

SELECT TRIM()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRIM()
FROM DUAL
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT TRIM(' Cat ', 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRIM(' Cat ', 2)
FROM DUAL
Error at Command Line : 1 Column : 20
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Also see RTRIM() and LTRIM() for more targeted functions for trimming each side of a string. These functions also allow you to trim multiple characters from the string.