Clever Geek Handbook

Replace only match beginning of line

I am trying to write a function to replace Romanian diacritics ( ĂƂƎȘȚ

) with their Latin letter equivalents ( AAIST

respectively).

The SQL Server replace

feature deals with Ă

, Ƃ

and Ǝ

just fine.

We seem to have a strange problem with Ș

and Ț

: they are only replaced if they are at the beginning of the line.

For example :

select replace(N'Ș', N'Ș', N'-')
-- '-'   # OK

select replace(N'ȘA', N'Ș', N'-')
-- '-A'  # OK

select replace(N'AȘ', N'Ș', N'-')
-- 'AȘ'  # WHAT??

select replace(N'ȘAȘ', N'Ș', N'-')
-- '-AȘ' # WHAT??

I was able to reproduce this behavior on both SQL Sever 2008 R2 and SQL Server 2012.

Is there an explanation for these seemingly strange results? Or could it just be a mistake?

My default database collation is SQL_Latin1_General_CP1_CI_AS

.

+1


source to share


1 answer


This is a sorting problem.
I had to reproduce at first because it was incredible, but your request had the same problem for me.

If you try with the correct sort, it works:



select replace(N'AȘ' COLLATE Latin1_General_BIN, N'Ș', N'-')
+1


source






More articles:


All Articles