Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

The accepted answer doesn't handle string containing only line break characters.

Here is a function that handles it properly, based on Martin's solution.

-- Based on https://dba.stackexchange.com/questions/137200/trim-trailing-whitespace-tab-newline-carriage-returns-etc/137203#137203
CREATE OR ALTER FUNCTION [dbo].[ufn_trim]
(
    @text nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN

    DECLARE @pattern nvarchar(50) = 
        N'%[^'

        -- common white space
        + nchar(0x0009) -- tab
        + nchar(0x000a) -- line feed (lf)
        + nchar(0x000b) -- line tabulation
        + nchar(0x000c) -- form feed
        + nchar(0x000d) -- carriage return (cr)
        + nchar(0x0020) -- space (sp)
        + nchar(0x00a0) -- no-break space (nbsp)

        -- uncommon spaces
        --+ nchar(0x1680) -- ogham space mark
        --+ nchar(0x180e) -- mongolian vowel separator (mvs)
        --+ nchar(0x2000) -- en quad
        --+ nchar(0x2001) -- em quad
        --+ nchar(0x2002) -- en space
        --+ nchar(0x2003) -- em space
        --+ nchar(0x2004) -- three-per-em space
        --+ nchar(0x2005) -- four-per-em space
        --+ nchar(0x2006) -- six-per-em space
        --+ nchar(0x2007) -- figure space
        --+ nchar(0x2008) -- punctuation space
        --+ nchar(0x2009) -- thin space
        --+ nchar(0x200a) -- hair space
        --+ nchar(0x202f) -- narrow no-break space (nnbsp)
        --+ nchar(0x205f) -- medium mathematical space (mmsp)
        --+ nchar(0x3000) -- ideographic space

        + N']%' COLLATE latin1_general_100_bin2
    ;

    DECLARE 
        @start int = PATINDEX(@pattern, @text),
        @end int = PATINDEX(@pattern, REVERSE(@text))
        ;

    IF @start = 0
    BEGIN
        -- the string doesn't contain any non-space character
        RETURN '';
    END

    RETURN SUBSTRING(@text, @start, LEN(@text + '..') - @start - @end);
END
GO