none
Need good function to trim all trailing white spaces from string RRS feed

  • Question

  • RTRIM will remove trailing spaces, but it wont remove tabs, carriage returns, line feeds or anything else white.  also, this function should remove any and all of these even if they are repeated.  For example"

    DECLARE @sometext NVARCHAR(max)
    SET @sometext = 'my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) '     ' +CHAR(10)+CHAR(10)+CHAR(10)

    The result should be:  'my text'

    Thanks in advance.

    Saturday, December 21, 2019 9:19 PM

Answers

  • Thank you all for the responses above.

    When I added similar text as leading white spaces, "CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)" in front of @sometext - not all the leading characters were removed.

    The solution by Yitzhak Khabinsky worked really good except that it also removed these characters inside the text.  I have the asusmption that if they are inside the text then they should remain there.

    Therefore I find the cleanest solution is to create a CLR function and just use the c# trim method:

    return myString.Trim();

    Thank  you again for all the replies.

    • Marked as answer by moondaddy Saturday, December 28, 2019 6:29 PM
    Saturday, December 28, 2019 6:29 PM

All replies

  • It can't be denied that the best would be to implement this as a CLR function where you easily can do this with RegExps.

    If you are on SQL 2017, Python is an alternative, although you would need to model it different, as you cannot really write a function in Python. Rather you would have to pass the data set to the Python script and then write back.

    For pure T-SQL... replacing the tabs etc with a plain space can be done with a number of nested replace, or with the TRANSLATE function added in SQL 2016 (or was that SQL 2017?). But collapsing multiple spaces to a single - that is not particularly fun.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, December 21, 2019 10:35 PM
  • It can't be denied that the best would be to implement this as a CLR function where you easily can do this with RegExps.

    And even more easily with String.Trim() in the .NET world.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, December 21, 2019 10:38 PM
  • And even more easily with String.Trim() in the .NET world.

    But it only seems to deal with leading and trailing spaces, which you can deal with easily in T-SQL as well. To collapse internal multiple spaces, you would need to use a RegEx replace.

    The only environment that I recall on the top of my head that has a built-in collapse() function is DCL, the command-line langauge for VMS - now that was a while ago.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, December 21, 2019 11:16 PM
  • Hi moondaddy,

    You can try the following udf:

    /*
    1. All invisible TAB, Carriage Return, and Line Feed characters will be replaced with spaces.
    2. Then leading and trailing spaces are removed from the value. 
    3. Further, contiguous occurrences of more than one space will be replaced with a single space.
    */
    CREATE FUNCTION dbo.udf_tokenize(@input VARCHAR(MAX))
       RETURNS VARCHAR(MAX)
    AS
    BEGIN 
       RETURN (SELECT CAST('<r><![CDATA[' + @input + ']]></r>' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
    END

    Test:

    DECLARE @sometext NVARCHAR(max);
    SET @sometext = 'my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) + '     ' +CHAR(10)+CHAR(10)+CHAR(10);
    
    SELECT @sometext, LEN(@sometext), dbo.udf_tokenize(@sometext), LEN(dbo.udf_tokenize(@sometext));
    Sunday, December 22, 2019 12:16 AM
  • String.Trim() will remove not just spaces, but other whitespace like tabs, newlines, et. al. as defined by IsWhitespace. To wit, with the string value in the question:

    $sometext = "my text   `t`n`r `n`n`n"
    Write-Host "String length is $($sometext.Length)"
    Write-Host "Trimmed string length is $($sometext.Trim().Length)"
    

    String length is 17
    Trimmed string length is 7


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, December 22, 2019 1:25 AM
  • Here's an example of SQLCLR function that wraps the .NET trim methods. Although one would typically use a VS project under source control for the assembly, I used a PowerShell to build the assembly here for illustration.

    PowerShell script to create the assembly:

    Add-Type @"
    using System;
    using System.Data;
    using System.Data.SqlTypes;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString Trim(string value)
        {
            if(value == null)
                return null;
            return value.Trim();
        }
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString TrimStart(string value)
        {
            if(value == null)
                return null;
            return value.TrimStart();
        }
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString TrimEnd(string value)
        {
            if(value == null)
                return null;
            return value.TrimEnd();
        }
    }
    "@ -ReferencedAssemblies System.Data, System.Xml -OutputAssembly "C:\temp\TrimFunctions.dll"

    T-SQL install DDL:

    EXEC sp_configure 'clr strict security',0;
    RECONFIGURE;
    CREATE ASSEMBLY TrimFunctions
    FROM 'C:\temp\TrimFunctions.dll';
    GO
    
    CREATE FUNCTION [dbo].[Trim] (@value nvarchar(MAX))  
    RETURNS nvarchar(MAX)
    AS EXTERNAL NAME [TrimFunctions].[UserDefinedFunctions].[Trim];  
    GO  
    
    CREATE FUNCTION [dbo].[TrimStart] (@value nvarchar(MAX))  
    RETURNS nvarchar(MAX)
    AS EXTERNAL NAME [TrimFunctions].[UserDefinedFunctions].[TrimStart];  
    GO  
    
    CREATE FUNCTION [dbo].[TrimEnd] (@value nvarchar(MAX))  
    RETURNS nvarchar(MAX)
    AS EXTERNAL NAME [TrimFunctions].[UserDefinedFunctions].[TrimEnd];  
    GO  
    

    Usage example:

    DECLARE @sometext NVARCHAR(max);
    SET @sometext = 'my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+'     ' +CHAR(10)+CHAR(10)+CHAR(10);
    SELECT DATALENGTH(dbo.TrimEnd(@sometext));
    GO
    

    I'll add that the XML method Yitzhak suggested is more convenient, it will not perform as well as the SQLCLR.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, December 22, 2019 2:51 AM
  • Hi,

    DECLARE @sometext NVARCHAR(max)
    SET @sometext = 'my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) +'     ' +CHAR(10)+CHAR(10)+CHAR(10)

    select @sometext, len(@sometext),
    reverse(substring(reverse(@sometext),patindex('%[a-z0-9]%',reverse(@sometext)),len(@sometext))),
    len(reverse(substring(reverse(@sometext),patindex('%[a-z0-9]%',reverse(@sometext)),len(@sometext))))

    Mark as answer if it helps. Thanks.

    Sunday, December 22, 2019 3:49 AM
  • In order to remove specific whitespaces from both sides, try this expression too (if available):

       TRIM(NCHAR(9) + NCHAR(10) + NCHAR(13) + NCHAR(32) from @sometext)

    Can be extended for all known whitespaces. (The list can be got from Unicode documentation).

    If you also want to merge interior repeated spaces, it probably can be done with a combination of TRANSLATE and REPLACE.

    • Edited by Viorel_MVP Sunday, December 22, 2019 9:18 AM
    Sunday, December 22, 2019 9:10 AM
  • I see now that Moondaddy actually only asked for trimming trailing white-space. I was bewildered by that long string and thought that he/she also wanted to collapse multiple sequences within the string to a single one.

    For simply trimming trailing white-space I would consider a combination of trim and translate:

    DECLARE @sometext NVARCHAR(max)
    SET @sometext = 'my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) + '     ' +CHAR(10)+CHAR(10)+CHAR(10)
    SELECT '<' + trim(translate(@sometext, nchar(9)+nchar(10)+nchar(13)+nchar(160), space(4))) + '>'

    Caveats:
    * Requires SQL 2017 or later.
    * You need to know which white-space values you want to work with. The .NET function will handle that for you (as long as you agree!)
    * It will also replace tabs etc inside the string, which may or may not be desirable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 22, 2019 11:10 AM
  • Hi moondaddy,

    Please check it.

    DECLARE @sometext NVARCHAR(max)
    DECLARE @len NVARCHAR(max)
    SET @sometext = REPLACE(REPLACE(REPLACE(REPLACE('my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+'     ' +CHAR(10)+CHAR(10)+CHAR(10),char(9),' '),char(10),' '),CHAR(13),' '),CHAR(32),' ')
    SET @sometext = RTRIM(@sometext)
    SELECT @len=len(RTRIM(@sometext))
    print(@sometext)
    print(@len)
    /*
    my text
    7
    */
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 23, 2019 3:21 AM
  • Just for your sample string, use three replace function with rtrim should work.

    DECLARE @sometext NVARCHAR(max)
    SET @sometext = 'my text   ' + CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) +'     ' +CHAR(10)+CHAR(10)+CHAR(10)
    --SET @sometext ='my test       '

    SELECT  
     REPLACE(REPLACE(REPLACE(RTRIM(@sometext),CHAR(9), ''),CHAR(10),''),CHAR(13),'') 
     ,
    len(REPLACE(REPLACE(REPLACE(RTRIM(@sometext),CHAR(9), ''),CHAR(10),''),CHAR(13),'') )

    Monday, December 23, 2019 3:23 PM
    Moderator
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 26, 2019 2:46 AM
  • Thank you all for the responses above.

    When I added similar text as leading white spaces, "CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)" in front of @sometext - not all the leading characters were removed.

    The solution by Yitzhak Khabinsky worked really good except that it also removed these characters inside the text.  I have the asusmption that if they are inside the text then they should remain there.

    Therefore I find the cleanest solution is to create a CLR function and just use the c# trim method:

    return myString.Trim();

    Thank  you again for all the replies.

    • Marked as answer by moondaddy Saturday, December 28, 2019 6:29 PM
    Saturday, December 28, 2019 6:29 PM