Stripping RTF new line values from field

Database
Enthusiast

Stripping RTF new line values from field

Hi all,

I have a field that is a varchar(10000) which contains the ¶ symbol when I select from it. It seems I don't have access to the otranslate function (SELECT Failed. 5589: Function 'OTRANSLATE' does not exist)

If I try select translate(field using unicode_to_latin) as "field name", i get "SELECT Failed. 6706. The string contains an untranslatable character." I get the same result if i try to do a translate_chk or if i replace with latin_to_unicode.

The closest thing I've gotten to is just doing a find/replace in excel, but I would prefer to get everything done within the query itself. Any advice?

1 REPLY
Senior Apprentice

Re: Stripping RTF new line values from field

There's translate(field using unicode_to_latin WITH ERROR) to replace invalid characters with the error character '1A'xc. But this error character could lead to untranslatable character errors in other functions and you might need udf_checklatin from the Unicode Tool Kit.

What's your Teradata release? 

oTranslate is built-in since TD14, maybe there's a an old C-UDF in SYSLIB which doesn't support Unicode, try td_sysfnlib.oTranslate instead. 

Check the actual character, '¶'  is valid in Latin, might be CR/LF, '0D0A'xc, but that's also valid.

In worst case you got the error character and then see above