How to remove control character CTRL+Z or x'1A' or substitute characte

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

How to remove control character CTRL+Z or x'1A' or substitute characte

Hi Team,

We are using Teradata 14.10 . There is a special character in one column we need to remove them using query function.

 

We tried OTRANSLATE . it is able to remove all other control character X'0A ,x'0B,x'0C',x'0D'  as below

 

cast(otranslate(DESCRIPTION,x'0A' x'0B' x'0C' x'0D' ,'') as varchar(100))

But we are not able to remove x'1A' .

Please assist us  Is there any other functions available to remove the CRL+Z character


Accepted Solutions
Teradata Employee

Re: How to remove control character CTRL+Z or x'1A' or substitute characte

All three parameters need to have the same character set, character literals are UNICODE (even if entered as single byte values), and U+001A cannot be directly entered and will cause implicit translation to fail. You can leverage the CHR function which returns CHARACTER SET LATIN, and also need to avoid implicit translations.

 

CHR(26) returns LATIN x'1A'

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

 

If DESCRIPTION is a LATIN column, explicitly translate the empty replacement string so all three are LATIN:

td_sysfnlib.otranslate(DESCRIPTION,CHR(10)||CHR(11)||CHR(12)||CHR(13)||CHR(26),translate('' using unicode_to_latin))

 

If DESCRIPTION is a UNICODE column then explicitly translate the match string so all three are UNICODE:

td_sysfnlib.otranslate(DESCRIPTION,translate(CHR(10)||CHR(11)||CHR(12)||CHR(13)||CHR(26) using latin_to_unicode with error),'')

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: How to remove control character CTRL+Z or x'1A' or substitute characte

All three parameters need to have the same character set, character literals are UNICODE (even if entered as single byte values), and U+001A cannot be directly entered and will cause implicit translation to fail. You can leverage the CHR function which returns CHARACTER SET LATIN, and also need to avoid implicit translations.

 

CHR(26) returns LATIN x'1A'

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

 

If DESCRIPTION is a LATIN column, explicitly translate the empty replacement string so all three are LATIN:

td_sysfnlib.otranslate(DESCRIPTION,CHR(10)||CHR(11)||CHR(12)||CHR(13)||CHR(26),translate('' using unicode_to_latin))

 

If DESCRIPTION is a UNICODE column then explicitly translate the match string so all three are UNICODE:

td_sysfnlib.otranslate(DESCRIPTION,translate(CHR(10)||CHR(11)||CHR(12)||CHR(13)||CHR(26) using latin_to_unicode with error),'')

Enthusiast

Re: How to remove control character CTRL+Z or x'1A' or substitute characte

Thanks alot the solution works perfectly for me