To Eliminate Non-ASCII column Value records and to Filter control characters

Tools & Utilities

To Eliminate Non-ASCII column Value records and to Filter control characters

Hi,

I am having a table in Teradata that may contain Non-ASCII characters (UTF-8 Character Set) ["Ãlafur Andri", "Bérengère"] and some control characters [^§ ¬ ¶^§ ¬––¶ ] and non-printable characters in it.

I am using FastExport utility to export the records into a delimited flat file in unix environment. Delimited by "|".

My requirement is
1. To discard the records that are having Non-ASCII characters in it.
2. To filter the control and non-printable characters from the records.

So that my output file will have only ASCII character set values without any control characters and non-printable characters in it.

Kindly help me to achieve this through Teradata BTEQ script or options available in Fastexport utility.

Note that:
1. It is possible to discard the Non-ASCII character records in ORACLE using condition
"WHERE = CONVERT(,'US7ASCII')". So that column values having Non-ASCII characters in it will be discarded.

2. Using Translate command in ORACLE we can filter the control characters & non-printable characters.

Please let me know if any equivalent commands available in Teradata.

Thanks in advance...

2 REPLIES
Junior Contributor

Re: To Eliminate Non-ASCII column Value records and to Filter control characters

1. Unicode -> ASCII
select
TRANSLATE(mycol using unicode_to_latin)
...
where TRANSLATE_CHK(mycol using unicode_to_latin) = 0

2. Oracle's TRANSLATE is implemented as a UDF (called otranslate) & available at teradata.com

Dieter
Enthusiast

Re: To Eliminate Non-ASCII column Value records and to Filter control characters

Hi Dnoeth,

How are you ?I need some help from your side...Reg :Unprintable characters in Teradata database version 12. If possible can you please share your mail id ,then i will send my problem with details ...

Thanks in advance ...or please text mail to me ..

My ID :  vvdrmca@gmail.com..I am waiting your reply