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

After reading my article SQL SERVER – 2008 – TRIM() Function – User Defined Function, I have received email and comments where user are asking if it is possible to remove trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc.

I found following script posted by Russ and Erik. It is modified a bit from original script.

CREATE FUNCTION dbo.LTrimX(@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 = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
RETURN @str
END
GO
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.LTrimX(REVERSE(@str)))
RETURN @str
END
GO
CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
dbo.LTrimX(dbo.RTrimX(@str))
END
GO
/* Run the created function */
SELECT dbo.TRIMX('         word leading trailing spaces           ') AS 'TrimmedWord'
GO

Running above function will give you result where there is no white spaces or any other while space like chars.

Here is the quick video on the same subject:

Let me know what you think about this and if this can be improved further.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. Hi Pinal,

    May I suggest some improvements?

    I tend to call my whitespace trimmers “SuperTrim” functions, so please excuse the difference in naming.

    (This will probably lose its indenting when I paste it.)

    ———
    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;
    ———

    I think it’s a big performance increase, because it does a numeric test of the first character, rather than a relatively expensive pattern test. If the first character is not a space or some other control character, no pattern is tested at all.

    Then, instead of testing for specific control characters, why not test for the whole range of zero (0) through 32 (space)?

    The function above does the test for first non-whitespace char and the trimming all in one statement.

    I couldn’t think of a faster or more efficient way to do it. I did some testing, and it seemed to work well.

    I haven’t looked at different ways to do SuperTrimRight yet, but the ways you outlined should work with this function OK.

    Thanks for the great SQL blog!

    -Todd (“Speednet”)

  2. Oh dear, surely, unless I’m missing something these functions will just return the first 8000 characters if they do a Trim, and so could cause havoc if someone uses it in SQL Server 2005/8 without realising its limitations, or you have it in place in a production system and the data changes. You’d do better if it took and returned Varchar(8000). Then it might flag up the weakness in the algorithm.

  3. The function definitely just returns the first 8000 characters. This version definitely works a lot better

    alter FUNCTION dbo.LTrimX(@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 =STUFF ( @str , 1 , PATINDEX(‘%[^' + @trimchars + ']%’, @str)-1 ,” )
    RETURN @str
    END
    GO

  4. Pinal, thanks for all your posts. May I suggest using the spelling “Enhanced” instead of “Enhenced” in your post?

    Thanks again!

  5. Pingback: SQL SERVER – Connect Item – Vote for Feature Request Function TRIM Journey to SQL Authority with Pinal Dave

  6. Pinal,

    In my working environment we are accepting multiple inputs (parameters) and we have to trim the white spaces before and after the input value.

    I have the function which performs very badly, takes lots of time. Now we are in plan of moving to SQL server 2008. I have heard that there is a function trim() available in SQL 2008. Can you please help me with the code which trims spaces for multiple comma separated input values.

  7. is it possible to trim data based on the number of characters and not just the spaces in an sql query. example: i just wanna get the last 3 digits of an int column which already has 50000 rows.

  8. Hi,

    I am using this awesome script, but I ran into a problem
    It trims letter N at the end of the field

    So if have have LTrim(RTrim(‘Ray Ban’) => the result will show Ray Ba

    any suggestions on how to fix this?

    thanks
    Igor

  9. Once issue I encountered using these functions was that it doesn’t remove char(160) (Otherwise known as whitespace). Spent a long time trying to ascertain why a row in our database still had a whitespace even after trimming.

    Only figured it out by using SELECT ASCII() and realising it wasn’t a space.

    If anyone has this issue add CHAR(160) to the trimchars parameter.

  10. Once issue I encountered using these functions was that it doesn’t remove char(160) (Otherwise known as non-breaking space). Spent a long time trying to ascertain why a row in our database still had a whitespace even after trimming.

    Only figured it out by using SELECT ASCII() and realising it wasn’t a normal space.

    If anyone has this issue add CHAR(160) to the trimchars parameter.

  11. is there anyway we can avoid the addition of these trailing 0’s after the decimal value for numeric field at the database level?

      • Hi Madhivanan,
        For eg: i have a column amount numeric(23,8).
        If i save 4.5, it is stored as 4.50000000.
        I want it to be stored as 4.5
        All the blogs on the internet have mentioned about handling such situations by trimming the trailing zeros in the application.
        I was just wondering if sqlserver has any setting to be done at the database level to handle this.

        Thanks,
        SP

  12. Pingback: SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video « SQL Server Journey with SQL Authority

  13. Pinal’s script works perfect for the line feeds and tabs at the end which doesnt work out by just using Trim or Ltrim or Rtrim.

  14. Hi Dave, et. al,

    What about these small modifications?
    1. making sure the string is null-checked
    2. instead of 8000, just use the length (or datalength) of the original string?

    IF ISNULL(@str, ”) LIKE ‘[' + @trimchars + ']%’
    SET @str = SUBSTRING(@str, PATINDEX(‘%[^' + @trimchars + ']%’, @str), len(@str))

    RETURN @str

    What do you think?

    Thanks!

    P.S. I tried the Speednet’s While/Stuff method, and it was just too slow for me. I like this method better because it’s not looping through every character, it’s just finding the first non-offensive character and doing a substring of the remainder of the string.

  15. did any one tested RTrimX on strlen() > 8000? I should think that due to use of reverse() and substring(.. , 8000) you may get data corruption.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s