Oracle TRIM Function

Oracle TRIM() function removes spaces or specified characters from the begin, end or both ends of a string.

Syntax

The following illustrates the syntax of the Oracle TRIM() function:

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] trim_source)
Code language: SQL (Structured Query Language) (sql)

Arguments

The Oracle TRIM() function accepts three arguments:

1) LEADING, TRAILING, BOTH

The first argument allows you to specify which side of the string to trim.

LEADING removes any leading character that equals the specified trim_character.
TRAILING removes any trailing character that equals the specified trim_character.
BOTH removes any leading and trailing character that equals the specified trim_character.

The first argument is optional. If you don’t specify it explicitly, the TRIM() function will remove both leading and trailing characters from the trim_source.

2) trim_character

is the specified character that should be removed from leading, trailing, or both of the trim_source.

3) trim_source

is the string where the trim_character should be removed.

The data types of trim_character and trim_source can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Return value

The TRIM() function returns a string where the trim_character is removed from leading, trailing, or both of the trim_source.

The data type of the result string is VARCHAR2 if the trim_source is a character data type or LOB in case the trim_source is a LOB data type.

The result string has the same character set as the trim_source.

Examples

The following statement removes both leading and trailing spaces from the ' ABC ' string.

SELECT
  TRIM( '  ABC  ' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

'ABC'
Code language: SQL (Structured Query Language) (sql)

The following statement removes leading spaces from the ' ABC ' string:

SELECT
  TRIM( LEADING ' ' FROM  ' ABC ' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

'ABC  '
Code language: SQL (Structured Query Language) (sql)

The following statement removes trailing spaces from the ' ABC ' string:

SELECT
  TRIM( TRAILING ' ' FROM  '  ABC  ' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

'  ABC'
Code language: SQL (Structured Query Language) (sql)

The following example removes the leading zero (0) from a string of numbers:

SELECT
  TRIM(LEADING  '0' FROM  '00012345' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The result is:

'12345'    
Code language: SQL (Structured Query Language) (sql)

Consider the following contacts table from the sample database.

This example uses the TRIM() function to update and remove all leading and trailing blanks of the first name and last name of all contacts:

UPDATE   
   contacts
SET 
   first_name = TRIM(first_name),
   last_name = TRIM(last_name);Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle TRIM() function to remove unwanted characters from the leading, trailing, or both of a string.

Was this tutorial helpful?