Trimming fields in SQL

Question

I imported data from Excel tables into SQL and a lot of fields had white spaces at their beginnings and ends.

I tried

update Table_Names set Name_ID = replace(Name_ID, ' ', '')

and

update Table_Names set Name_ID = LTRIM(Name_ID)

and

Update TableName set Name_ID = LTRIM(RTRIM(Name_ID))

Each one of those returned the number of rows affected. But then when I selected the fields again, they still had white spaces at the beginning and end.

Any idea why this is happening and how to solve it?

Any help would be appreciated

score 1 · Answer 1 · answered Oct 03 '13 at 13:43

1

It's possible that there are tab characters, which trim doesn't eliminate. Try this:

update tablename set name_id = replace(name_id, char(9), '')

Carriage returns and line feeds are sometimes imported as well. You can follow the same pattern above using char(10) and char(13) to eliminate those.

answered Oct 03 '13 at 13:43

Derek

21,828
7
53
61

Good point. Tried char(9), char(10) and char(13) but same results kept occurring.. Is there another alternative? – HelpASisterOut Oct 03 '13 at 13:50

score 1 · Answer 2 · answered Oct 03 '13 at 13:58

In general Tabs, Line feeds, carriage returns are the culprits but if you encounter white space characters that can not be removed then you can create a function like :

CREATE FUNCTION [dbo].[fn_Trim] 
(
    @str as varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN   
    --Replace all whitespace characers with Characer 32 whitespace

    --NULL
    Set @str = Replace(@str,CHAR(0),CHAR(32));
    --Horizontal Tab
    Set @str = Replace(@str,CHAR(9),CHAR(32));
    --Line Feed
    Set @str = Replace(@str,CHAR(10),CHAR(32));
    --Vertical Tab
    Set @str = Replace(@str,CHAR(11),CHAR(32));
    --Form Feed
    Set @str = Replace(@str,CHAR(12),CHAR(32));
    --Carriage Return
    Set @str = Replace(@str,CHAR(13),CHAR(32));
    --Column Break
    Set @str = Replace(@str,CHAR(14),CHAR(32));
    --Non-breaking space
    Set @str = Replace(@str,CHAR(160),CHAR(32));


    Set @str = LTRIM(RTRIM(@str));
    Return @str
END
GO

and then use it to update the columns as :

Update Table_Names 
set Name_ID = dbo.fn_Trim(Name_ID)

Trimming fields in SQL

2 Answers2