special character handing

Database
KN
Enthusiast

special character handing

Hello Team

 

How to remove the special character.. TD_SYSFNLIB.Otranslate doesn't seem to work as it gives untranslatable character. 

SyntaxEditor Code Snippet

XI900_900000_WINTERTRON �

 

Any reasons why otranslate shouldnt be working?

 

thanks

KN

1 REPLY
Highlighted
Teradata Employee

Re: special character handing

All three parameters need to have the same character set; if any of the three are UNICODE, the database will try to implicitly translate the others to UNICODE also. Character literals are UNICODE (even if entered as single-byte values), and the "error substitution" character cannot be directly entered. But you can leverage the CHR function which returns CHARACTER SET LATIN, and will also need to avoid implicit translations.

 

CHR(26) returns LATIN x'1A'

TRANSLATE(CHR(26) USING LATIN_TO_UNICODE WITH ERROR) returns UNICODE x'FFFD'

 

For example, if DESCRIPTION is a LATIN column, explicitly translate the replacement string (or we could use CHR(20) to replace with a blank) so all three are LATIN:

td_sysfnlib.otranslate(DESCRIPTION,CHR(26),translate('' using unicode_to_latin))

 

If DESCRIPTION is a UNICODE column then explicitly translate the match string instead:

td_sysfnlib.otranslate(DESCRIPTION,translate(CHR(26) using latin_to_unicode with error),'')