MS SQL Server TRIM() Function

Sometimes there may be want to remove unwanted space or other character from a string or field in MS SQL Server. There is a function called TRIM in MS SQL Server by which you can do this. TRIM function removes the space character char(32) or other specified characters from the start and end of a string.

Let’s see the function syntax:

TRIM ( [ characters FROM ] string )

In this syntax:

  • Parameter characters is a literal, variable, or function call of any non-LOB character type (nvarcharvarcharnchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren’t allowed..
  • Parameter string Is an expression of any character type (nvarcharvarcharnchar, or char) where characters should be removed..
  • Function returns a character expression with a type of string argument where the space character char(32) or other specified characters are removed from both sides. Returns NULL if input string is NULL.

By default, the TRIM function removes the space character from both the beginning and the ending ends of the string. This behavior is equivalent to LTRIM(RTRIM(@string)). The following example removes spaces from before and after the word freetechtrainer.com.

DECLARE @var varchar(30) = '     freetechtrainer.com     ';
SELECT TRIM(var) result;

Here is the result

result
freetechtrainer.com

The following example removes a trailing period and spaces from before f and after the word freetechtrainer.com.

DECLARE @var varchar(30) = ',.     freetechtrainer.com #     !';
SELECT TRIM(',.! ' FROM var) result;

Here is the result:

result
freetechtrainer.com #

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to removes the space character char(32) or other specified characters from the start and end of a string using TRIM function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.

Add a Comment