CODEWITHSUNDEEP
sql-serverreplace
JJANSSEN
JJANSSEN

Reputation: 171

SQL SERVER replace function working odd

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

Upvotes: 2

Views: 88

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46320

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.

Upvotes: 3

Related Questions