Search code examples
sql-servertranslatecase-sensitive

TRANSLATE() function case sensitivity and precedence issues

SELECT TRANSLATE('NEE', 'Ee', 'Ee')
--NEE
SELECT TRANSLATE('NEE', 'eE', 'eE')
--Nee
SELECT TRANSLATE('NÉE', 'Éé', 'Ee')
--NEE
SELECT TRANSLATE('NÉE', 'éÉ', 'eE')
--NeE

How do you prevent a match by a differently cased character ? In the above examples, output with lower case character were not what I expected.

Solution

  • For case-sensitive function calls, easiest is to use a case sensitive collation.

    Testscript:

    SELECT  TRANSLATE('NEE', 'Ee', 'Ee')
,   TRANSLATE('NEE', 'eE', 'eE')
,   TRANSLATE('NÉE', 'Éé', 'Ee')
,   TRANSLATE('NÉE', 'éÉ', 'eE')
,   TRANSLATE('NEE', 'Ee' collate latin1_general_cs_as, 'Ee' collate latin1_general_cs_as)
,   TRANSLATE('NEE', 'eE' collate latin1_general_cs_as, 'eE' collate latin1_general_cs_as)
,   TRANSLATE('NÉE', 'Éé' collate latin1_general_cs_as, 'Ee' collate latin1_general_cs_as)
,   TRANSLATE('NÉE', 'éÉ' collate latin1_general_cs_as, 'eE' collate latin1_general_cs_as)

    Outputs:

    (column 1) (column 2) (column 3) (column 4) (column 5) (column 6) (column 7) (column 8)
    NEE Nee NEE NeE NEE NEE NEE NEE