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

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 

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