Is it possible to Trim all the values in a column in a single statement?

Question

I've a table in a (MS) SQL database which has an Id column (identity, int) and a Name column (varchar(250)). However, the values in the name column contain (fairly random) leading and trailing spaces as I think they were cut and pasted from "something else" (no idea what!).

Is it possible in T-SQL to do the following:

update MyTable set Name = trim(name)

and have it update all the Name columns with the trimmed value?

score 66 · Accepted Answer · answered Oct 18 '11 at 07:33

66

MS SQL does not have a trim function. You'll need to use rTrim and lTrim together.

update MyTable set Name = lTrim(rTrim(name))

answered Oct 18 '11 at 07:33

Barry Jordan

2,666
2
22
24

score 10 · Answer 2 · answered Oct 18 '11 at 07:32

10

Try

update MyTable set Name = LTRIM(RTRIM((name))

answered Oct 18 '11 at 07:32

StuartLC

104,537
17
209
285

score 6 · Answer 3 · answered Jul 13 '21 at 21:10

6

Well as of SQL Server 2017 (14.x) and later there is a TRIM() function, so;

UPDATE MyTable set Name = TRIM(name)

Will work fine. Or in my case I also want to null empty fields after trimming.

UPDATE MyTable set Name = NULLIF(TRIM(name), '')

answered Jul 13 '21 at 21:10

bmiller

1,454
1
14
14

score 3 · Answer 4 · edited May 27 '15 at 03:36

3

Try this:

UPDATE [table] SET [column1] = REPLACE([column1],'i:0#.w|',' ')

edited May 27 '15 at 03:36

Bat_Programmer

6,717
10
56
67

answered May 27 '15 at 02:56

silentgut

87
2
9

This one is the only helping on replacing a given character across all values in a column. Can't believe TRIM only works with fixed strings... – Alfabravo Sep 18 '20 at 02:36

score 3 · Answer 5 · answered Oct 18 '11 at 07:33

3

You could try this:

UPDATE MyTable
SET Name = LTRIM(RTRIM(Name))

Take a look here to create a function inside your database to use it faster

answered Oct 18 '11 at 07:33

Marco

56,740
14
129
152

Thanks @Bazza answer appeared first, but thank you for the extra info. – BlueChippy Oct 18 '11 at 07:44

score 2 · Answer 6 · answered Oct 18 '11 at 07:33

2

In SQL Server there is only RTRIM and LTRIM, but you can use them both together:

update MyTable set Name = RTRIM(LTRIM((name))

answered Oct 18 '11 at 07:33

cjk

45,739
9
81
112

score 0 · Answer 7 · answered Jul 21 '21 at 16:52

0

check for isnull if you want the empty string returned for the null case

 UPDATE MyTable set Name = rtrim(ltrim(IsNull(name,'')))

answered Jul 21 '21 at 16:52

Golden Lion

3,840
2
26
35

score 0 · Answer 8 · answered Oct 18 '11 at 07:34

0

Not quite - there's no TRIM() function available, so you have to use RTRIM() and LTRIM() (right- and left-trim, respectively):

UPDATE MyTable set Name = rtrim(ltrim(name))

answered Oct 18 '11 at 07:34

Widor

13,003
7
42
64

Is it possible to Trim all the values in a column in a single statement?

8 Answers8