O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 '

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required