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'.

This is also working on a colleague's pc and we running the queries against the same database server — StevenMcD, Aug 24 '09 at 12:06

score 12 · Accepted Answer · edited Dec 01 '10 at 13:15

12

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.

edited Dec 01 '10 at 13:15

Peter Mortensen

30,738
21
105
131

answered Aug 24 '09 at 12:13

Jabezz

1,292
3
14
17

4

You can always do REPLACE('18 286.74', CHAR(160), '') in the case of non-breaking spaces – Jabezz Aug 24 '09 at 12:20
@Jabezz This unicode test feature is really cool! Was really helpful in diagnosing my mystery character :) – Shrout1 Sep 03 '13 at 18:43

score 0 · Answer 2 · answered Aug 24 '09 at 12:04

0

maybe cast is needed.

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

answered Aug 24 '09 at 12:04

x2.

9,554
6
41
62

Casting a varchar to a varchar, I doubt it but I'll try it – StevenMcD Aug 24 '09 at 12:06

score 0 · Answer 3 · answered Aug 24 '09 at 12:09

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?

score 0 · Answer 4 · answered Aug 24 '09 at 12:12

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?

score 0 · Answer 5 · edited Oct 26 '12 at 04:42

0

I was having the same issue and found that it was a char(10) (line feed). when copied out of Managment Studio it became a char(32) but in the record it was a char(10) try

Select Replace(@string, char(13), '')

edited Oct 26 '12 at 04:42

Andrew Barber

39,603
20
94
123

answered Jan 11 '12 at 23:56

Bryon Wheeler

1

T-SQL, Remove space in string

5 Answers5

Linked