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