SQL in a Nutshell, 3rd Edition

Name

TRIM

The TRIM function removes leading characters, trailing characters, or both from a specified character string or BLOB value. This function also removes other types of characters from a specified character string. The default behavior is to trim the specified character from both sides of the character string. If no removal character is specified, TRIM removes spaces by default.

ANSI SQL Standard Syntax

TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_char] FROM ]
  target_string
  [COLLATE collation_name] )

The removal_char is the character to be stripped out, and the target_string is the character string from which characters are to be stripped. If no removal_char is specified, TRIM strips out spaces. The COLLATE clause forces the result set of the function into another pre-existing collation set.

MySQL, Oracle, and PostgreSQL

These platforms support the ANSI SQL syntax of TRIM.

SQL Server

SQL Server provides the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively. On SQL Server, LTRIM and RTRIM cannot be used to trim other types of characters.

Examples

SELECT TRIM('   wamalamadingdong  ');
'wamalamadingdong'

SELECT LTRIM( RTRIM('   wamalamadingdong  ') );
'wamalamadingdong'

SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN');
'76 AMC GREMLIN'

SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx');
'WHISKEY'

SELECT TRIM(TRAILING 'snack' FROM 'scooby snack');
'scooby '