CODEWITHSUNDEEP
sqlreplace
William DeMasi
William DeMasi

Reputation: 13

Unexpected results when using Replace()

Trying to Replace() characters:

REPLACE(REPLACE(REPLACE(REPLACE(NetworkMemberId,'é','e'),'í','i'), 'ó','o'),'ñ','n')

but it is converting all e's, i's, o's, and n's, even upper case.

I tried using char(233), char(236), char(243) and char(241) with the same results.

Upvotes: 0

Views: 39

Answers (1)

Lynn Crumbling
Lynn Crumbling

Reputation: 13377

From https://msdn.microsoft.com/en-us/library/ms186862.aspx:

REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

Guessing you want to apply something like Latin1_General_CS_AS:

REPLACE(REPLACE(REPLACE(REPLACE(NetworkMemberId COLLATE 
Latin1_General_CS_AS,'é','e'),'í' COLLATE Latin1_General_CS_AS,'i'), 
'ó' COLLATE Latin1_General_CS_AS,'o'),'ñ' COLLATE 
Latin1_General_CS_AS,'n')

** EDIT **

Oops - missed accent sensitivity.

Upvotes: 1

Related Questions