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