TSQL Remove space in string

Question

I have 2 strings in SQL and the REPLACE function only works on 1 of them, why is that?

Example 1:

SELECT REPLACE('18 286.74', ' ', '')

Example 2:

SELECT REPLACE('z z', ' ', '')

Example 1's output is still "18 286.74" whereas Example 2's output is "zz". Why does SQL not react the same way to both strings?

UPDATE:

When running select replace('123 123.12', ' ', '') that works fine. still not with '18 286.74'

Answer 1

maybe cast is needed.

UPD: or not(on sql 2005 works fine too)

Answer 2

Are you sure it is a space? i.e. the same whitespace character that you are passing as the second argument? The code you've posted works fine for me on SQL Server 2008.

Re working on your friends PC - perhaps the whitespace got normalized when you sent it to him?

Answer 3

You are probably using non-breakable space.

I could reproduce it by typing ALT+0160 into the number in SELECT REPLACE('18 286.74', ' ', '')

Could you please issue this following:

SELECT CAST('18 286.74' AS BINARY), REPLACE('18 286.74', ' ', '')

by copying the '18 286.74' from REPLACE into CAST?

Answer 4

select unicode(substring('18 286.74', 3, 1))

if the code returned is 32 ... then its a space, if not, its a different unicode character and your replace ' ' wont work.