REPLACE not working on sql table column

Question

I've tried select REPLACE(' this is a user name', ' ', '') and it gives me 'thisisausername' which is supposed to be.

My problem is, when I try to use REPLACE on selecting a table column, it doesn't work!

My query:

SELECT REPLACE(UserName, ' ', '') as UserName FROM MY_TABLE

it still gives me usernames with spaces! Am I doing something stupid?

What database are you using? This works fine for me using MS SQL assuming username is a varchar(n). — jpw, Nov 03 '14 at 17:24
Perhaps they are non-standard unicode whitespace or tabs, see what `unicode(substring(fld, X, 1))` returns where X is the position of a space — Alex K., Nov 03 '14 at 17:24
Are you sure it's a space in your username column, and not a tab character? — Shiva, Nov 03 '14 at 17:24
@Shiva actually i'm not sure if it is a space or a tab char but i tried also this REPLACE(REPLACE(UserMain.UserName, ' ', ''), char(9), '') because i know that char(9) is a tab char if i'mright — Eytch, Nov 03 '14 at 17:30
What does the username string look like exactly? Can you give an example where it fails? — jpw, Nov 03 '14 at 17:32
@Kritner yes there is a period but also there is a space before letter N — Eytch, Nov 03 '14 at 17:37
what is `unicode(left(field, 1))` for the ` n.miranda` value? — Alex K., Nov 03 '14 at 17:38
@eytch could you try an LTrim(UserName) and see if you still have the leading character? Maybe it could be something that "looks" like a space. — Kritner, Nov 03 '14 at 17:39
Thats NO-BREAK SPACE (U+00A0) so `replace(fld, char(160), '')` for that specific case. I would normalize everything to ' ' and check the input sanitization. — Alex K., Nov 03 '14 at 17:42
Possible duplicate of [this question](http://stackoverflow.com/questions/1321946/t-sql-remove-space-in-string), check the comment under the [accepted answer](http://stackoverflow.com/a/1321992/1225845) — AHiggins, Nov 03 '14 at 17:43
@AlexK. it worked! thankyou so much, you can post it as an answer so i can mark it :) — Eytch, Nov 03 '14 at 17:44

score 5 · Accepted Answer · answered Nov 03 '14 at 17:47

@AlexK. it's 160 for unicode(left(field, 1))

160 is Unicode NO-BREAK SPACE so that's what you need to replace:

replace(UserName, char(160), '')

You could update everything replacing char(160) with a whitespace ' ' and then just use your original query in the future (perhaps also ensuring such values cannot be entered in the future)

REPLACE not working on sql table column

1 Answers1