BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Trimming Functions

The forty-seventh part of the SQL Server Programming Fundamentals tutorial continues the investigation of Transact-SQL (T-SQL) string processing functions. This article describes two functions that remove leading and trailing white space from strings.

Trimming Functions

Some operations cause unwanted white space to be included at the start or end of character data. For example, when user input includes additional spaces that are not required. Where it is possible to have this unwanted white space, you can remove it using the trimming functions provided by Transact-SQL (T-SQL). You may decide to do this within queries or insertions and updates. You could also make changes to the input parameters of your stored procedures before the passed information is used.

LTrim

The first of the two trimming functions is named LTrim. As the name suggests, this function removes spaces from the left side of a string. The function accepts a single argument, which is the string to be processed. All spaces up to the first non-white space character are discarded from the result of the function. Any further white space is unmodified.

The argument may be any data type that can be implicitly converted to a VarChar, except for Text, NText and Image types. Other data types must be cast or converted to a compatible type before they can be trimmed. The return value will be an NVarChar if the input contained Unicode information and a VarChar otherwise.

The following sample demonstrates the LTrim function. It removes the two leading spaces from the input string. The quotes (") are added so that you can see that these spaces have been trimmed.

PRINT '"' + LTRIM('  White space  ') + '"'        -- "White space  "

RTrim

The RTrim function is similar to LTrim. The only difference is that white space characters from removed from the right side of the string instead of the left.

PRINT '"' + RTRIM('  White space  ') + '"'        -- "  White space"

Unlike many languages, T-SQL does not provide a trim function that removes spaces from both ends of a string. However, this is not a problem as the LTrim and RTrim functions can be combined for the same result.

PRINT '"' + LTRIM(RTRIM('  White space  ')) + '"' -- "White space"
22 November 2009