SQL in a Nutshell

Name

TRIM

Synopsis

The TRIM function removes leading spaces, trailing characters, or both from a specified character string. 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 string is specified, TRIM removes spaces by default.

SQL99 Syntax

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

The removal_string is the character string to be stripped out. The target _string is the character string from which characters are to be taken. If a removal_string is not specified, then TRIM strips out spaces. The COLLATE clause forces the result set of the function into another preexisting collation set.

MySQL, PostgreSQL, and Oracle support the SQL99 syntax of TRIM.

Microsoft SQL Server (and the other vendors for that matter) provide the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively. LTRIM and RTRIM cannot be used to trim other types of characters.

Examples

SELECT TRIM('   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 '