Is there a Oracle SQL Function that TRANSLATEs or REPLACEs localized characters with basic ones?-CodePudding

I know about TRANSLATE & REPLACE, but I want to know if there is a function that does the conversion from any language letter to the 26 English letters without the need to specify the equivalences.

This is, the hypothetical function should know that "ä" is either "ae" or "a" without me having to write the specific TRANSLATE query:

SELECT TRANSLATE('Hände', 'ä', 'a') FROM DUAL;

Which would end up with me having to write a lengthy query for each equivalence, or having to write a DB Function just for that.

SELECT TRANSLATE('Hände', 'äëöåßñç', 'aeoasnc') FROM DUAL;

Furthermore, if there is a chance to get better equivalences (e.g. "ss" instead of "ß", or "oe" instead of "ö"), it would be better.

This is because I will perform several conversions for several strings & with several equivalences (some of them I might not know beforehand).

CodePudding user response：

Have you try utl_raw.cast_to_varchar2((nlssort(s, 'nls_sort=binary_ai'))) ?

CodePudding user response：

You can convert this to 7 bit ASCII:

SELECT CONVERT('Hände', 'US7ASCII') FROM dual;

(Not all special characters can properly converted, though. See this demo https://dbfiddle.uk/?rdbms=oracle_21&fiddle=e222a42f3241a53859f2193d317d710a where ø results in a mere questionmark.)