How do I remove non-breaking spaces from a column in SQL server?

Question

I'm trying to remove a non-breaking space (CHAR 160) from a field in my table. I've tried using functions like RTRIM() to get rid of it, but the value is still there.

What do I need to do to remove the non-breaking space from the column?

score 60 · Accepted Answer · answered Mar 05 '13 at 03:05

60

Try using REPLACE

UPDATE Your_Table
SET Your_Column = REPLACE(Your_Column, NCHAR(0x00A0), '')
WHERE Id = x

answered Mar 05 '13 at 03:05

Brian Rogers

125,747
31
299
300

score 20 · Answer 2 · edited Jun 20 '14 at 13:55

20

You could also use

REPLACE(The_txt, NCHAR(160), ' ')

edited Jun 20 '14 at 13:55

Robert Levy

28,747
6
62
94

answered Jun 20 '14 at 13:29

Totor777

301
2
3

I like this but for clarity perhaps: REPLACE(The_txt, NCHAR(160), NCHAR(32)) – SinisterPenguin Nov 03 '21 at 09:44
NCHAR was not working for me. CHAR(160) worked. As said by @Malikarjun, use CHAR instead of NCHAR. – Chetan Oswal Nov 26 '22 at 04:42

score 7 · Answer 3 · answered Oct 19 '18 at 10:30

7

If the above solutions does not work, try CHAR instead of NCHAR

UPDATE Your_Table
SET Your_Column = REPLACE(Your_Column, CHAR(160), '')
WHERE Id = x

CHAR worked for me.

answered Oct 19 '18 at 10:30

arjun

1,645
1
19
19

How do I remove non-breaking spaces from a column in SQL server?

3 Answers3

Linked

Related