Enhanced String Trimmer

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Phil Factor

Date: 28 jan 2011

Description

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)

Code

IF OBJECT_ID(N'udf_Trim') IS NOT NULL

DROP FUNCTION dbo.udf_Trim

GO

CREATE FUNCTION dbo.udf_Trim

/**

summary: >

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

example:

- 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 ')

returns:

Input string without trailing or leading blank characters, however these characters are defined in @BlankRange

**/ (@String VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE

@BlankRange CHAR(255),

@FirstNonBlank INT,

@LastNonBlank INT

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 + ']%',

REVERSE(@String)))

IF @FirstNonBlank > 0

RETURN SUBSTRING(@String,@FirstNonBlank, @LastNonBlank-@firstNonBlank)

RETURN '' --nothing would be left

END

GO