Enhanced String Trimmer
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Author: Phil Factor
Date: 28 jan 2011
The built-in LTRIM and RTRIM commands in SQL Server are good, but they can't deal effectively or reliably with non-breaking spaces and other control characters.
This function trims of all control characters from the leading and trailing edges of a string (the characters in the set to be trimmed can be configured)
IF OBJECT_ID(N'udf_Trim') IS NOT NULL
DROP FUNCTION dbo.udf_Trim
CREATE FUNCTION dbo.udf_Trim
This procedure returns a string with all leading and trailing blank space removed. It is similar to the TRIM functions in most current computer languages. You can change the value of the string assigned to @BlankRange, which is then used by the PATINDEX function. The string can be a rangee.g. a-g or a list of characters such as abcdefg.
Author: Phil Factor
Revision: 1.1 changed list of control character to neater range.
date: 28 Jan 2011
- select dbo.udf_Trim(' 678ABC ')
- select dbo.udf_Trim(' This has leading and trailing spaces ')
- select dbo.udf_Trim(' left-Trim This')
- select dbo.udf_Trim('Right-Trim This ')
Input string without trailing or leading blank characters, however these characters are defined in @BlankRange
**/ (@String VARCHAR(MAX))
IF @String IS NULL
RETURN NULL--filter out null strings
-- Here is where you set your definition of what constitutes a blank character.
-- We've just chosen every 'control' character, the space character and the
-- non-breaking space. Your requirements could be different!
SELECT @BlankRange = CHAR(0) + '- ' + CHAR(160)
SELECT @FirstNonBlank = PATINDEX('%[^' + @BlankRange + ']%', @String)
SELECT @lastNonBlank = 1 + LEN(@String + '|') - (PATINDEX('%[^' + @BlankRange + ']%',
IF @FirstNonBlank > 0
RETURN SUBSTRING(@String,@FirstNonBlank, @LastNonBlank-@firstNonBlank)
RETURN '' --nothing would be left