3

I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc.

There are a number of functions out there but the ones I found all have limitations, e.g. truncate the string to 8000 characters. e.g. (according to some of the comments):

SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why.

CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX)
    AS
    BEGIN
    IF (ASCII(LEFT(@str, 1)) < 33) BEGIN
        SET @str = STUFF(@str, 1, PATINDEX('%[^'+CHAR(0)+'-'+CHAR(32)+']%', @str) – 1, ' ');
    END;

    RETURN @str;
END;

So my question is, what is the best approach to accomplish the task above?

6

The best way would probably be to create a CLR function and use .NET framework libraries.

A TSQL attempt is below.

DECLARE @Pattern NVARCHAR (50) = N'%[^' + 
/*List from http://stackoverflow.com/a/18169122/73226 */
NCHAR(0x0009) +  /*tab*/
NCHAR(0x000A) +  /*line feed*/
NCHAR(0x000B) +  /*line tabulation*/
NCHAR(0x000C) +  /*form feed*/
NCHAR(0x000D) +  /*carriage return*/
/*Various spaces from https://www.cs.tut.fi/~jkorpela/chars/spaces.html */
NCHAR(0x0020) +  
NCHAR(0x00A0) +  
NCHAR(0x1680) +  
NCHAR(0x180E) +  
NCHAR(0x2000) +  
NCHAR(0x2001) +  
NCHAR(0x2002) +  
NCHAR(0x2003) +  
NCHAR(0x2004) +  
NCHAR(0x2005) +  
NCHAR(0x2006) +  
NCHAR(0x2007) +  
NCHAR(0x2008) +  
NCHAR(0x2009) +  
NCHAR(0x200A) +  
NCHAR(0x200B) +  
NCHAR(0x202F) +  
NCHAR(0x205F) +  
NCHAR(0x3000) +  
NCHAR(0xFEFF) +
N']%' COLLATE Latin1_General_100_BIN2;

WITH T(String) AS
(
SELECT '         leading only' union all
SELECT '

Line breaks

' union all
SELECT '    tab and space    ' union all
SELECT '' 
)
SELECT '[' + SUBSTRING(String,Start,len(String + '..') - Start - Finish) + ']'
FROM T
CROSS APPLY
(
SELECT  PATINDEX(@Pattern,String),
        PATINDEX(@Pattern,REVERSE(String))
)ca(Start, Finish)

The [ and ] delimiters are applied just to make it easier to verify there is no white space lurking there. Remove these when you're happy this is the case.

2
  • Just FY: 1) the full and current list of White Space characters is available at: unicode.org/cldr/utility/… . The page you linked to includes two extra Code Points: "U+200B and U+FEFF are not included in the [Unicode] table of space characters, as they have no width and are not supposed to have any visible glyph.", 2) The _BIN Collations are deprecated. The preferred binary Collation is Latin1_General_100_BIN2, and 3) String_Trim and RegEx_Replace are available in the Free version of SQL# (which I created). May 2 '16 at 18:23
  • @srutzky - Thanks. I've updated the collation I'll leave fixing the specific white space characters as an exercise to the reader. May 3 '16 at 12:12
4

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why.

Because you copy pasted from the website and the page doesn't show a hyphen/minus but a en-dash.

You can just position your cursor behind it and delete it, then type minus on your keyboard and the code will work.

If you want to modify the code from the comments to also perform the right trim scenario you can do as the article does and create a function that just reverses the string twice (code copied from the article but changed to use SuperTrimLeft).

CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.SuperTrimLeft(REVERSE(@str)))
RETURN @str
END
GO
CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN dbo.SuperTrimLeft(dbo.RTrimX(@str))
END
GO
0
4

The two answers given so far both entail at least one reversal of the string to tackle the right hand side.

This method doesn't.

CREATE FUNCTION dbo.LTrimWhitespace(@Input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Output VARCHAR(MAX)
    DECLARE @Whitespace VARCHAR(20)
    DECLARE @Pos INT

    SET @Whitespace = CHAR(0) + CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32)
    SET @Pos = 1

    WHILE CHARINDEX(SUBSTRING(@Input, @Pos, 1), @Whitespace) > 0
    BEGIN
        SET @Pos = @Pos + 1
    END

    SET @Output = RIGHT(@Input, LEN(@Input) - @Pos + 1)

    RETURN @Output
END

CREATE FUNCTION dbo.RTrimWhitespace(@Input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Output VARCHAR(MAX)
    DECLARE @Whitespace VARCHAR(20)
    DECLARE @Pos INT

    SET @Whitespace = CHAR(0) + CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32)
    SET @Pos = LEN(@Input)

    WHILE CHARINDEX(SUBSTRING(@Input, @Pos, 1), @Whitespace) > 0
    BEGIN
        SET @Pos = @Pos - 1
    END

    SET @Output = LEFT(@Input, @Pos)

    RETURN @Output
END
2
  • Thank you, but there is a bug in each routine: they malfunction if there are trailing spaces, because LEN excludes trailing spaces. Perhaps either RTrim first or use DATALENGTH (but beware of double length due to encoding).
    – Pete Alvin
    Jan 7 '20 at 15:25
  • For left version this fixes it: SET @Input = RTrim(@Input)
    – Pete Alvin
    Jan 7 '20 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.