SQL SERVER replace function working odd

Question

Why do i get only one Result?

    select replace(N'2',N'2','X') as Text
    union 
    select replace(N'2',N'²','X') as Text

The second SELECT statement includes a squared.

@@version= Microsoft SQL Server 2012 - 11.0.5623.0

Are you certain that you really entered a squared into the query? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 27, 2016 at 10:47
Here is the proof select 1 where N'²'=N'2' returns 1. Looks like in SQL SERVER '²'='2'. So both the selects replaced with X — Pரதீப்
– Pரதீப், Commented Aug 27, 2016 at 10:56
However the ASCII value differs. SELECT ASCII('²'),ASCII('2') returns 178 and 50 respectively — Pரதீப்
– Pரதீப், Commented Aug 27, 2016 at 11:00
Replace as a function is limited in part to your environment. For example, ASCII codes 32 and 255 (space and non-breaking space) are treated exactly the same unless specifically called. Your environment shows a space, but they are not. In the same way,you may find using the actual ASCII code in your function ensures accuracy. Basically, are you sure REPLACE is actually reading it like you think it is? — clifton_h
– clifton_h, Commented Aug 27, 2016 at 12:57

Dan Guzman · Accepted Answer · 2016-08-27 15:09:20Z

3

The behavior depends on the case-sensitivity of the collation:

SELECT REPLACE(N'2' COLLATE Latin1_General_CS_AS, N'2'  COLLATE Latin1_General_CS_AS,'X'  COLLATE Latin1_General_CS_AS) as Text
UNION 
SELECT REPLACE(N'2' COLLATE Latin1_General_CS_AS, N'²'  COLLATE Latin1_General_CS_AS,'X'  COLLATE Latin1_General_CS_AS) as Text
GO
--results:
X
2
SELECT REPLACE(N'2' COLLATE Latin1_General_CI_AS, N'2'  COLLATE Latin1_General_CI_AS,'X'  COLLATE Latin1_General_CI_AS) as Text
UNION 
SELECT REPLACE(N'2' COLLATE Latin1_General_CI_AS, N'²'  COLLATE Latin1_General_CI_AS,'X'  COLLATE Latin1_General_CI_AS) as Text
GO
--results:
X

When no collation is specified for literal expressions, the database default collation is used.

answered Aug 27, 2016 at 15:09

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

SQL SERVER replace function working odd

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related