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"?

Wyndham

Answer 1

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

Glimpse

You solution is working, because returning 46783815@@, this gonna work always? no matter how many ASCII characters are in string? – Wyndham

It takes into account the usual culprits, so yes you should be OK with this. – Glimpse

I have a data at this link and it's not working , please have a look: #29101326 – Alleviative

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! – Gardy

Answer 2

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))

Eggert

@Eggert - thanks for this, didn't know about the ASCII function! – Cammack

Answer 3

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))))

Ascospore

Answer 4

How about this?

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

Chauvin

Answer 5

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

Mis

Answer 6

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)

Ingrid

Answer 7

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%')

Leicester