Untranslatable Character When Using oREPLACE in Teradata

I'm trying to replace double spaces in a text field with just one space. I'm using Teradata but I am not sure which version. I use Toad Data Point to query the data.

I've used a few different methods to get this done including REGEXP_REPLACE, OREPLACE, AND TRANSLATE but I keep getting an error thrown: [Teradata Database] [6706] The string contains an untranslatable character.

So, I thought to use TRANSLATE_CHK in a CASE statement but I get the same error.

Here is my latest attempt.

select TSB.SESSION_CREW_MBRS, TRANSLATE_CHK(TSB.SESSION_CREW_MBRS USING LATIN_TO_UNICODE) as TransChk, CASE WHEN TRANSLATE_CHK(TSB.SESSION_CREW_MBRS USING LATIN_TO_UNICODE) = 0 THEN OREPLACE(TSB.SESSION_CREW_MBRS, ' ', ' ') ELSE TSB.SESSION_CREW_MBRS END AS RepChk
FROM myDatabase TSB

Any ideas?

1 Answer

Try something like this to translate the "error substitution character" to a different LATIN character (in this example, to ¿ inverted question mark) before removing the excess spaces:

select TSB.SESSION_CREW_MBRS, TRANSLATE_CHK(TSB.SESSION_CREW_MBRS USING LATIN_TO_UNICODE) as TransChk, OREPLACE(OTRANSLATE(TSB.SESSION_CREW_MBRS,CHR(26),CHR(191)),' ', ' ') AS RepChk
FROM myDatabase TSB