TRANSLATE() function case sensitivity and precedence issues

Question

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.

Answer 1

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