Using below statment, I found a untranslatable character in a VARCHAR column
SELECT COL FROM TABLE WHERE TRANSLATE_CHK (COL USING LATIN_TO_UNICODE)<>0;
'XXXX □ YYYY'
Character is the little square as shown above.
I cannot use update table, cannot touch source table, only option is SELECT and get output as 'XXXX YYYY' .
I tried oreplace,otranslate in SELECT , which says "Failed. 6706: The string contains an untranslatable character. "
you can use the output of the translate_chk function in combination with substring to trim out the errant character in a select statement.
select substring(col from 1 to (translate_chk(col using latin_to_unicode) -1))||'XXXX YYYY'||substring(col from (translate_chk(col using latin_to_unicode)+1) to char_length(col))
--no guarantee the parenthesis match in the example, but it illustrates the concept