remove LF CR SQL

Ashish Patel

Trim not removing spaces in SQL

When I Was importing text file to SQL server, sometime it include spaces with data as well. And That space it not remove by using Trim function.These spaces appear as white spaces to our eyes but this is not the truth. When I check it with ASCII(char) it shows char 10- Line Feed(LF), 13 – Carriage Return(CR), 160 –nbsp. So the solution is to replace these characters with white spaces (CHAR(32)) and then apply the TRIM function to it. Below is the screipt for it:

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([ColumnName], CHAR(10), CHAR(32)),
                                        CHAR(13), CHAR(32)), CHAR(160),
                                CHAR(32)))) AS [ColumnName] 
FROM [TableName]

Update:: include tab – CHAR(9)

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([ColumnName], CHAR(10), CHAR(32))
                                  ,CHAR(13), CHAR(32))
                                  ,CHAR(160), CHAR(32))
                                  ,CHAR(9),CHAR(32))))
FROM [TableName]

Thanks
[ad#inpost]