Search code examples
sqlteradataregexp-replace

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?

Solution

  • 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