Oracle TRIM() Function

The Oracle (PL/SQL) TRIM() function removes the specified character either from the beginning or the end of a string.

Syntax

TRIM( [LEADING | TRAILING | BOTH]  [trim_character] FROM string)

Parameters

Return Value

Returns the trimmed version of the specified string.

Example 1:

The example below shows the usage of TRIM() function.

TRIM('  SQL Tutorial    ')
Result: 'SQL Tutorial'

TRIM(BOTH ' ' FROM '  SQL Tutorial    ')
Result: 'SQL Tutorial'

TRIM(TRAILING ' ' FROM '  SQL Tutorial    ')
Result: '  SQL Tutorial'

TRIM(LEADING ' ' FROM '  SQL Tutorial    ')
Result: 'SQL Tutorial    '

TRIM(' ' FROM '  SQL Tutorial    ')
Result: 'SQL Tutorial'

TRIM(LEADING '0' FROM '000123')
Result: '123'

TRIM(TRAILING '1' FROM 'John1')
Result: 'John'

TRIM(TRAILING '3' FROM 'John123')
Result: 'John12'

TRIM(BOTH '1' FROM '123John321')
Result: '23John32'

Example 2:

Consider a database table called Employee. When the following INSERT statements are executed, the Name column will contain records with leading and trailing spaces.

INSERT INTO Employee VALUES ('        John   ', 'London', 3000);
INSERT INTO Employee VALUES ('        Marry   ', 'New York', 2750);
INSERT INTO Employee VALUES ('      Jo    ', 'Paris', 2800);
INSERT INTO Employee VALUES ('      Kim    ', 'Amsterdam', 3100);

-- see the result
SELECT * FROM Employee;

The query will produce the following result:

To remove the leading and trailing spaces from the Name column of the Employee table, the following query can be used:

UPDATE Employee SET Name = TRIM(Name);

-- see the result
SELECT * FROM Employee;

This will produce the following result: