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 |