Same old Untranslatable character

Database

Same old Untranslatable character

Hello , 

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. "

Any inputs 

Regards,

Sri

Tags (1)
1 REPLY
Enthusiast

Re: Same old 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