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?

Are you sure it's a space in your username column, and not a tab character? — Shiva
– Shiva, Commented Nov 3, 2014 at 17:24
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.
– Alex K., Commented Nov 3, 2014 at 17:42

Alex K. · Accepted Answer · 2014-11-03 17:47:24Z

5

@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)

answered Nov 3, 2014 at 17:47

Alex K.

177k32 gold badges275 silver badges298 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

REPLACE not working on sql table column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related