SQL Server : How to replace whitespaces(&nbsp, ASCII) in a string with numbers?

Question

This works select replace('string with space',' ','') and returns stringwithspace.

This does not work select replace('8 190',' ',''), it returns 8 190

This is the same select replace('8 190',' ','') returns 8 190.

Why doesn't it work?

Solution

The string_pattern needs to contain the ASCII code 160 for the space &nbsp

SELECT REPLACE('8 190', char(160),'')

Comments

[Claudiu Haidu]

Yeah, sorry for the typo, I wanted to correct that, as it returned the same result, I later found out that, the space character (' ') was in fact &nbsp which has the ASCII code 160.

[Bhavin Thummar]

It is worked for me. I was directly passed " " in second parameter but when I have passed "char(160)" at second parameter then I got the result and save my time to solved my query related this. Thanks Buddy.