Trimming Whitespace Without Affecting Internal Whitespace

Question

I need to trim whitespace (including tabs, newlines, etc.) from a string without affecting the internal whitespace. For example:



foo
bar
    baz

Would become

foo
bar
    baz

Of course LTRIM / RTRIM won't suffice because they only remove spaces. There are a few posts here that show using the REPLACE method to handle other characters (e.g. this one), but of course that will also remove the internal characters. I wasn't able to find an example on here to show how to remove only the leading and trailing whitespace characters from a string.

Solomon Rutzky · Answer 1 · 2014-09-29T06:16:27.340

This functionality is easily handled by SQLCLR. You can write your own function that does a simple String.Trim(), or you can download an already written function, such as the String_Trim() function from the SQL# library. I am the author of SQL#, but the String_Trim function (and many others, including Regular Expressions, etc.) are available in the Free version.

String_Trim() removes whitespace (tabs, newlines, carriage returns, and spaces) from both ends of the string without touching any whitespace between the non-whitespace characters. If you copy and paste the example code below, you will see that before and after the non-whitespace characters, as well as between them, is a mix of each type of whitespace character (well, tabs are converted to spaces here so I had to put in the tabs explicitly).

It is as simple as:

PRINT N'~~' + SQL#.String_Trim(N'   ' + NCHAR(9) + N'   
    ' + NCHAR(9) + N'   gfgj    
lf      ' + NCHAR(9) + N'      

g  

' + NCHAR(9) + N'       g        
    ' + NCHAR(9) + N'   ') + N'~~';

Output:

~~gfgj  
lf             

g

    g~~

The script I was working on was part of a maintenance process. I needed something that was fully self-contained and wouldn't rely on any other pre-existing objects. Still, I can see that library could come in very handy. I may end up refacoring my script to take advantage of some of its other features. +1 Because it would likely useful to future readers. — p.s.w.g, Sep 29 '14 at 18:47

score 0 · Answer 2 · answered Sep 28 '14 at 23:46

Here's my current solution, but I'd welcome other suggestions. For VARCHAR values:

DECLARE @str varchar(8000) , 
        @pat varchar(100) = '%[^ ' + CHAR(9) + '-' + CHAR(13) + ']%';

SET @str = /* load value */

SET @str = SUBSTRING( @str , PATINDEX( @pat , @str ) , 
        DATALENGTH( @str ) - 
        PATINDEX( @pat , @str ) - 
        PATINDEX( @pat , REVERSE( @str )) + 2 );

But because NVARCHAR characters take up 2 bytes, you'll need to divide DATALENGTH by 2 in that case:

DECLARE @str nvarchar(4000) , 
        @pat varchar(100) = '%[^ ' + CHAR(9) + '-' + CHAR(13) + ']%';

SET @str = /* load value */

SET @str = SUBSTRING( @str , PATINDEX( @pat , @str ) , 
        DATALENGTH( @str ) / 2 - 
        PATINDEX( @pat , @str ) - 
        PATINDEX( @pat , REVERSE( @str )) + 2 );

Note that the pattern used here (@pat) only covers ASCII characters. That was sufficient for my requirements, but if full Unicode support is necessary, you can extend the pattern. See Wikipedia: Whitespace character for a full list.

And of course, it would be fairly easy to convert this to a UDF for easy reuse.

Navneet · Answer 3 · 2014-09-29T04:57:28.680

0

You Should try this :

DECLARE @testString varchar(255)
set @testString = 'MY STRING      '

/*I always use this like a final solution*/
SET @testString = REPLACE(REPLACE(REPLACE(@testString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '')
 SELECT @testString   

/*
CHAR(9)       - Tab
CHAR(10) - New Line
CHAR(13) - Carriage Return
*/

edited Sep 29 '14 at 04:57

answered Sep 29 '14 at 04:49

Navneet

447
4
13

This won't work. As I mentioned in the question, I need to preserve internal whitespace, but `REPLACE` will remove the specified characters anywhere in the string. – p.s.w.g Sep 29 '14 at 05:00
dude we replace only CHAR(9) CHAR(10) and CHAR(13) with blank. – Navneet Sep 29 '14 at 05:12
I understand that, but the problem is, it should only remove those characters when they appear at the beginning or end of the string. I need to keep them when they appear in the middle of the string. See the example above if it's not clear. – p.s.w.g Sep 29 '14 at 05:21
Check my new Answer on TOP – Navneet Sep 29 '14 at 07:20

Navneet · Answer 4 · 2014-09-29T07:18:36.640

Use below query this works :

    DECLARE @trimchars VARCHAR(10),@str varchar(max)  
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)  
set @str='test string            it is'
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)  
IF @str LIKE '%[' + @trimchars + ']' SET @str = SUBSTRING(reverse(@str), PATINDEX('%[^' + @trimchars + ']%', reverse(@str)), 8000)  
select @str

score 0 · Answer 5 · answered Dec 01 '14 at 14:11

I found this here, and changed it a bit. It would work in your case. Any suggestions or feedback would be much appreciated.

Go

CREATE FUNCTION dbo.LeftTrim(@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


CREATE FUNCTION dbo.RightTrim(@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.LeftTrim(REVERSE(@str)))

    RETURN @str
END
GO

CREATE FUNCTION dbo.SuperTrim(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN dbo.LeftTrim(dbo.RightTrim(@str))
END
GO

-- Illustration

DECLARE @testStr AS VARCHAR(MAX)=''
SELECT @testStr='my first line ' + CHAR(13) + CHAR(10)  + CHAR(13) + CHAR(10)  + CHAR(13) + CHAR(10)  + 'second line ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

SELECT dbo.SuperTrim(@testStr) + '--end of line'

score 0 · Answer 6 · answered Jan 18 '20 at 10:29

Check and Try the below script (Unit Tested)-

--Declaring Table Variable
DECLARE @Tbl TABLE(col_1 VARCHAR(100));

--Test Samples
INSERT INTO @Tbl (col_1)
VALUES
('  EY     y            
Salem')
, ('  EY     P    ort       Chennai   ')
, ('  EY     Old           Park   ')
, ('  EY   ')
, ('  EY   ')
,(''),(null),('d                           
    f');

SELECT col_1 AS INPUT,
    LTRIM(RTRIM(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(col_1,CHAR(10),' ')
        ,CHAR(11),' ')
        ,CHAR(12),' ')
        ,CHAR(13),' ')
        ,CHAR(14),' ')
        ,CHAR(160),' ')
        ,CHAR(13)+CHAR(10),' ')
    ,CHAR(9),' ')
    ,' ',CHAR(17)+CHAR(18))
    ,CHAR(18)+CHAR(17),'')
    ,CHAR(17)+CHAR(18),' ')
    )) AS [OUTPUT]
FROM @Tbl;

Trimming Whitespace Without Affecting Internal Whitespace

6 Answers6