TRIM

The TRIM function removes all instances of some specified character (default: blanks) from the input string. By default, TRIM removes the character from the beginning and end of the input string (BOTH). TRIM can remove the character from just the beginning of the string (LEADING) or the end of the string (TRAILING).

Syntax

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

Remarks

•

If the input string is NULL, the output is also NULL. Otherwise, the output is a string.

•

If you also want to trim characters within a string, use the REPLACE function. (See REPLACE.)

•

When no character to trim is specified, the TRIM function removes ASCII space characters (value 32), but not Unicode nonbreaking space characters (value 160).

The following table lists the valid input types, and their corresponding output types.

Data Type of string	Output Type
CHAR, LONGVARCHAR, VARCHAR, NULL	Same as the input data type.

Examples

This example removes all leading and trailing ASCII space characters from the string, resulting in ‘ababa’:

SELECT TRIM ('  ababa  ')

FROM /services/databases/system/DUAL

This example is equivalent to the one above:

SELECT TRIM (BOTH '  ababa  ')

FROM /services/databases/system/DUAL

This TRIM function results in bab:

SELECT TRIM (BOTH 'a' FROM 'ababa')

FROM /services/databases/system/DUAL

This TRIM function results in baba:

SELECT TRIM (LEADING 'a' FROM 'ababa')

FROM /services/databases/system/DUAL

This TRIM function results in abab:

SELECT TRIM (TRAILING 'a' FROM 'ababa')

FROM /services/databases/system/DUAL