How to remove white space characters from a string in SQL Server

Question

I'm trying to remove white spaces from a string in SQL but LTRIM and RTRIM functions don't seem to work?

Column:

[ProductAlternateKey] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL

Query:

select REPLACE(ProductAlternateKey, ' ', '@'),
       LEN(ProductAlternateKey),
       LTRIM(RTRIM(ProductAlternateKey))      AS LRTrim,
       LEN(LTRIM(RTRIM(ProductAlternateKey))) AS LRLen,
       ASCII(RIGHT(ProductAlternateKey, 1))   AS ASCIIR,
       ASCII(LEFT(ProductAlternateKey, 1))    AS ASCIIL,
       ProductAlternateKey
from DimProducts
where ProductAlternateKey  like '46783815%'

Result:

|  COLUMN_0 | COLUMN_1 | LRTrim | LRLen | ASCIIR | ASCIIL | PRODUCTALTERNATEKEY |
---------------------------------------------------------------------------------
|  46783815 |        8 | 46783815|     8|   53   |   52   |            46783815 |
| 46783815  |        10|46783815  |   10|   10   |   52   |           46783815  |

Can it be other symbols if LTRIM and RTRIM are not working, like "Enter"?

DMK · Accepted Answer · 2016-01-25T09:34:52.933

Using ASCII(RIGHT(ProductAlternateKey, 1)) you can see that the right most character in row 2 is a Line Feed or Ascii Character 10.

This can not be removed using the standard LTrim RTrim functions.

You could however use (REPLACE(ProductAlternateKey, CHAR(10), '')

You may also want to account for carriage returns and tabs. These three (Line feeds, carriage returns and tabs) are the usual culprits and can be removed with the following :

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ProductAlternateKey, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

If you encounter any more "white space" characters that can't be removed with the above then try one or all of the below:

--NULL
Replace([YourString],CHAR(0),'');
--Horizontal Tab
Replace([YourString],CHAR(9),'');
--Line Feed
Replace([YourString],CHAR(10),'');
--Vertical Tab
Replace([YourString],CHAR(11),'');
--Form Feed
Replace([YourString],CHAR(12),'');
--Carriage Return
Replace([YourString],CHAR(13),'');
--Column Break
Replace([YourString],CHAR(14),'');
--Non-breaking space
Replace([YourString],CHAR(160),'');

This list of potential white space characters could be used to create a function such as :

Create Function [dbo].[CleanAndTrimString] 
(@MyString as varchar(Max))
Returns varchar(Max)
As
Begin
    --NULL
    Set @MyString = Replace(@MyString,CHAR(0),'');
    --Horizontal Tab
    Set @MyString = Replace(@MyString,CHAR(9),'');
    --Line Feed
    Set @MyString = Replace(@MyString,CHAR(10),'');
    --Vertical Tab
    Set @MyString = Replace(@MyString,CHAR(11),'');
    --Form Feed
    Set @MyString = Replace(@MyString,CHAR(12),'');
    --Carriage Return
    Set @MyString = Replace(@MyString,CHAR(13),'');
    --Column Break
    Set @MyString = Replace(@MyString,CHAR(14),'');
    --Non-breaking space
    Set @MyString = Replace(@MyString,CHAR(160),'');

    Set @MyString = LTRIM(RTRIM(@MyString));
    Return @MyString
End
Go

Which you could then use as follows:

Select 
    dbo.CleanAndTrimString(ProductAlternateKey) As ProductAlternateKey
from DimProducts

You solution is working, because returning `46783815@@`, this gonna work always? no matter how many ASCII characters are in string? — Justin, Jan 08 '13 at 09:39
It takes into account the usual culprits, so yes you should be OK with this. — DMK, Jan 08 '13 at 09:45
I have a data at this link and it's not working , please have a look: http://stackoverflow.com/questions/29100826/data-is-splitting-into-multiple-rows-while-copying-from-sql-server-2012-to-excel?noredirect=1#comment46439769_29100826 — rohit singh, Mar 19 '15 at 12:15
Really late to this answer, but it is fantastic. I had a bunch of blank little squares at the end of each value in a column value and it turned out to be ASCII code 8, which is a backspace? Who knew! — Mark Moretto, Apr 19 '19 at 14:29

score 4 · Answer 2 · answered Jan 08 '13 at 09:00

4

In that case, it isn't space that is in prefix/suffix.
The 1st row looks OK. Do the following for the contents of 2nd row.

ASCII(RIGHT(ProductAlternateKey, 1))

and

ASCII(LEFT(ProductAlternateKey, 1))

answered Jan 08 '13 at 09:00

shahkalpesh

33,172
3
63
88

@shahkalpesh - thanks for this, didn't know about the ASCII function! – Dibstar Jan 08 '13 at 10:25

TechDo · Answer 3 · 2013-01-08T09:37:46.260

4

There may be 2 spaces after the text, please confirm. You can use LTRIM and RTRIM functions also right?

LTRIM(RTRIM(ProductAlternateKey))

Maybe the extra space isn't ordinary spaces (ASCII 32, soft space)? Maybe they are "hard space", ASCII 160?

ltrim(rtrim(replace(ProductAlternateKey, char(160), char(32))))

edited Jan 08 '13 at 09:37

answered Jan 08 '13 at 09:00

TechDo

18,398
3
51
64

score 0 · Answer 4 · answered Jan 08 '13 at 09:22

0

How about this?

CASE WHEN ProductAlternateKey is NOT NULL THEN
CONVERT(NVARCHAR(25), LTRIM(RTRIM(ProductAlternateKey))) 
FROM DimProducts
where ProductAlternateKey  like '46783815%'

answered Jan 08 '13 at 09:22

Vishwanath Dalvi

35,388
41
123
155

Raj · Answer 5 · 2013-01-08T09:37:27.443

0

Looks like the invisible character -

ALT+255

Try this

select REPLACE(ProductAlternateKey, ' ', '@')
--type ALT+255 instead of space for the second expression in REPLACE 
from DimProducts
where ProductAlternateKey  like '46783815%'

Raj

Edit: Based on ASCII() results, try ALT+10 - use numeric keypad

edited Jan 08 '13 at 09:37

answered Jan 08 '13 at 09:29

Raj

10,653
2
45
52

rlhane · Answer 6 · 2023-07-21T18:31:15.500

This is a pretty old post, but for the sake of others, by default, TRIM only trims standard spaces, but you can readily override that. It's pretty readable (and if you don't want to remove the whitespace within the string, it's the function to use). Just add a 'FROM' clause within the TRIM, listing all of the characters that might be present as an expression before 'FROM', like...

TRIM(' $' + char(9) + char(10) + char(13) FROM ProductAlternateKey)

would remove leading and trailing spaces, dollar signs, tabs, returns and linefeeds from the string. You can, of course, assign the expression to a variable and reference it as needed in your code to make it more readable and maintainable...

TRIM(@whitespace FROM ProductAlternateKey)

score -3 · Answer 7 · edited Sep 23 '15 at 18:10

Remove new line characters with SQL column data

Update a set  a.CityName=Rtrim(Ltrim(REPLACE(REPLACE(a.CityName,CHAR(10),' '),CHAR(13),' ')))
,a.postalZone=Rtrim(Ltrim(REPLACE(REPLACE(a.postalZone,CHAR(10),' '),CHAR(13),' ')))  
From tAddress a 
inner Join  tEmployees p  on a.AddressId =p.addressId 
Where p.MigratedID is not null and p.AddressId is not null AND
(REPLACE(REPLACE(a.postalZone,CHAR(10),'Y'),CHAR(13),'X') Like 'Y%' OR REPLACE(REPLACE(a.CityName,CHAR(10),'Y'),CHAR(13),'X') Like 'Y%')

How to remove white space characters from a string in SQL Server

7 Answers7

Linked