Programming Solutions

Your Source for Information

Removing Leading and Trailing White Space Characters in TSQL

by Maeenul 3. April 2010 14:33

Often we need to trim a string to remove the leading and trailing whitespace characters.

SQL server does not provide a TRIM() function.

But it provides LTRIM() and RTRIM() functions.

LTRIM() is essentially a function that removes the spaces from the left of a string, in other words the leading spaces.

Similarly RTRIM() function removes the spaces from the right of a string, in other words the trailing spaces.

But none of these functions removes other whitespace characters like tab, newline, carriage return and line feed.

In this post we will create a user defined function (UDF) that trims the spaces from head and tail and also remove the other whitespace characters from the string.

The signature of LTRIM() and RTRIM() functions are as bellow:

LTRIM (character_expression )

RTRIM (character_expression )

 

Here character_expression is the string that we want to be trimmed.

declare @str as nvarchar(max)

set @str = ' A string with leading and trailing spaces '

select 'Prev_String'+LTRIM(@str)+'Next_String'

Returned result is: Prev_StringA string with leading and trailing spaces Next_String

select 'Prev_String'+RTRIM(@str)+'Next_String'

Returned result is:

Prev_String A string with leading and trailing spacesNext_String

Now if we want to trim both the leading and trailing spaces, then we can simply use both the functions.

select 'Prev_String'+LTRIM(RTRIM(@str))+'Next_String'

Returned result is:

Prev_StringA string with leading and trailing spacesNext_String

We can also create an UDF with this so that we don't need to always use both LTRIM and RTRIM.

 

CREATE FUNCTION DBO.TRIM(@STR NVARCHAR(MAX))

RETURNS NVARCHAR(MAX)

BEGIN

RETURN LTRIM(RTRIM(@STR))

END

We can use

SELECT TRIM(@str)

Now this is only the first part of this post. We have only removed spaces.

What about other white space characters?

Often we need to load data from csv or fixed length files and we have some invisible characters at the end of our string.

LTRIM() or RTRIM() is not going to help us in those cases.

These other whitespace characters include tab(\t), carriage return (cr), line feed(lf) or newline (\n).

To remove these characters we can replace these characters with a blank character.

 

SELECT REPLACE(@str, '\t', ")

But this is not going to work and \t is not the actual character for tab.

We will need to use the unicode character values of these whitespace characters.

SQL server provides CHAR() function that takes an integer value and returns the unicode character for the corresponding integer code.

The CHAR function has the following signature:

CHAR (integer_expression )

The unicode value of the whitespace characters are:

Control character

Value

Tab

char(9)
Line feed

char(10)
Carriage return

char(13)
Space

Char(32)

So to remove tabs from a string we can use

REPLACE(@str, CHAR(9), ")

Similarly we can remove the Line Feed and Carriage return.

We assume that tab, line feed and carriage returns are not acceptable characters anywhere in a string.

So we can finally create our whitespace removal function as:

CREATE FUNCTION DBO.TRIM(@STR NVARCHAR(MAX))

RETURNS NVARCHAR(MAX)

BEGIN

declare @TAB nvarchar(2), @LF nvarchar(2), @CR nvarchar(2),@NL varchar(2)

set @TAB = char(9)

set @LF = char(10)

set @CR = char(13)

set @NL = char(13)+char(10)

return replace(replace(replace(replace(LTRIM(RTRIM(@STR))

, @TAB, "), @NL, "), @LF, "),@CR, ")

END 


Tags: , , , ,

Category: TSQL

Comments (8)


Comments (8) -

  2. Your Trim removes all TAB, LF, CR and NL not only leading or trailing ones. When using REPLACE it replaces all patterns it finds, anywhere.
  2. Nice article.
  2. [...] blog.programmingsolution.net/.../removing-leading-and-trailing-white-space-charac... [...]
  2. Your function REPLACES the other characters, but it does not TRIM them. Some of us are working with data like addresses where we want the char(13) + char(10) where they make a new address line, but not those that are just hanging out at the end making blank new lines.
  2. Hi Heiko, I have mentioned in my post "Similarly we can remove the Line Feed and Carriage return. We assume that tab, line feed and carriage returns are not acceptable characters anywhere in a string". The function I have written uses this assumption.
  2. Nice TRIM() function. Thanks for the code.
    PaulG
  2. An alternate approach is to look for the first occurrence of a character you want , trim up to here and reverse the remainder and do this again. Finally re-reverse the string.

    Here's a function I wrote that you ccan use to do this.

    note that this requires a number table which is best stored so you dont have to keep recreating it.

    This is my code so please use it in entirety including my header comment

    Dave



    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:    David Bridge
    --                      David Bridge Technology Limited
    --                      www.davidbridgetechnology.com
    -- Create date: April 2011
    -- Description:  Returns a fully trimmed varchar
    -- ignore whitespace, tabs, carriage returns etc until you find something.
    -- Save the string
    -- reverse it
    -- do it again
    -- return the string
    -- =============================================
    ALTER FUNCTION dbo.ALLTRIM
    (
      @str varchar(max)
    )
    RETURNS varchar(max)
    AS
    BEGIN
      
      DECLARE @ReturnString  varchar(max)  = ''  -- default in case of nothing useful in string
      DECLARE @tmpString    varchar(max)  = @str
      DECLARE @StringLength  int        = DATALENGTH(@tmpString)
      
      -------------------------------------------------------------------------------------------------------
      --Create 0 based number table (best making a permanent one of these and indexing it properly for reuse)
      DECLARE @number TABLE (number int PRIMARY KEY)
      DECLARE @increment int = 0
      while (@increment < 1000)
      BEGIN
        INSERT INTO @number (number) VALUES (@increment)
        SET @increment = @increment + 1
      END
      -------------------------------------------------------------------------------------------------------

      -- Main part of function
      -- Find position of the first useful char
      DECLARE @StartPosition int
      SELECT top 1
        @StartPosition = Number
      FROM @number
      WHERE
        Number <= DATALENGTH(@tmpString) - 1
        AND SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[0-9]'
        OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[a-z]'
        OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[A-Z]'
      
      if @StartPosition IS NOT NULL
      BEGIN
        -- found a useful char so trim trailing chars to here    
        SET @tmpString = SUBSTRING(@tmpString,@StartPosition+1,@StringLength)
          
        -- Now just reverse the string and do the same again
        SET @tmpString = REVERSE(@tmpString)

        SELECT top 1
          @StartPosition = Number
        FROM @number
        WHERE
          Number <= DATALENGTH(@tmpString) - 1
          AND SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[0-9]'
          OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[a-z]'
          OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[A-Z]'
        
        if @StartPosition IS NOT NULL
          SET @tmpString = SUBSTRING(@tmpString,@StartPosition+1,@StringLength)

        -- and finally re-reverse the string
        SET @ReturnString = REVERSE(@tmpString)
      END  
      
      RETURN @ReturnString

    END
    GO
  1. Anthony Evans Anthony Evans says: May 20. 2011 at 07:48
  2. DECLARE @x VARCHAR(8000)
    SET @x = 'Boohoo1  ' + CHAR(9) + CHAR(13) + '   ';
    SELECT REVERSE(SUBSTRING(REVERSE(@x),PATINDEX('%[a-z,0-9]%',REVERSE(@x)),DATALENGTH(@x)))

Add comment

biuquote
  • Comment
  • Preview
Loading

Alpha Tags