T-SQL, Remove space in string

Question

I have two strings in SQL and the REPLACE function only works on one 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'.

Solution

Test it the following way.

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

If the code returns 32 then it's a space, if not, it's a different Unicode character and your replace ' ' won't work.

Comments

[StevenMcD]

Casting a varchar to a varchar, I doubt it but I'll try it

[Jabezz]

You can always do REPLACE('18 286.74', CHAR(160), '') in the case of non-breaking spaces

[Shrout1]

@Jabezz This unicode test feature is really cool! Was really helpful in diagnosing my mystery character :)