SQL in a Nutshell, 2nd Edition

Name

TRIM

Synopsis

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 function is to trim the specified character from both sides of the character string. If no removal character is specified, TRIM removes spaces by default.

SQL2003 Syntax

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

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

DB2

DB2 provides the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively.

MySQL, Oracle, and PostgreSQL

These platforms support the SQL2003 syntax of TRIM.

Microsoft SQL Server

SQL Server provides the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively. On SQL Server the 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'); ...