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
– StevenMcD, Commented Aug 24, 2009 at 12:06

Peter Mortensen · Accepted Answer · 2010-12-01 13:15:07Z

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 1, 2010 at 13:15

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Aug 24, 2009 at 12:13

Jabezz

1,3323 gold badges14 silver badges17 bronze badges

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

2 Comments

Jabezz Over a year ago

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

Shrout1 Over a year ago

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

x2. · Accepted Answer · 2009-08-24 12:04:38Z

0

maybe cast is needed.

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

answered Aug 24, 2009 at 12:04

x2.

9,6586 gold badges43 silver badges63 bronze badges

1 Comment

StevenMcD Over a year ago

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

Marc Gravell · Accepted Answer · 2009-08-24 12:09:46Z

0

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?

answered Aug 24, 2009 at 12:09

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Quassnoi · Accepted Answer · 2009-08-24 12:12:27Z

0

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?

answered Aug 24, 2009 at 12:12

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Comments

Andrew Barber · Accepted Answer · 2012-10-26 04:42:57Z

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, 2012 at 4:42

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

answered Jan 11, 2012 at 23:56

Bryon Wheeler

1

Collectives™ on Stack Overflow

T-SQL, Remove space in string

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related