Unable to remove carriage returns and line feed from columns

Database
Enthusiast

Unable to remove carriage returns and line feed from columns

Hi All,

I am trying to select the data from a column for downstream without CR and LF.

But unable to do so.

I tried the following

select oTranslate(col, '0A0D'xc,'')

but it throws untranslatable character error.

 

So i tried to convert it into unicode and tried the below command,the command works but the data still has new line fields (CR and LF)

select oreplace(translate(load_hash_key_val using latin_to_unicode WITH ERROR),'0A0D'xc,' ') from table;

 

 

My requirement is simple,

I need to select the column after removing CR and LF from the column.

Note: The column has some hssh keys and hash values.

 

P.S I am not a Teradata developer, but i need this selection for downstream during sqoop import query:)

 

Hope someone help me out here. Thanks in Advance


Accepted Solutions
Teradata Employee

Re: Unable to remove carriage returns and line feed from columns

 

For your purposes, an OTRANSLATE (character by character replacement) seems more appropriate than OREPLACE (string search and replacement).

 

When you specify character literals in hex, it depends on your session character set as to whether the hex values are interpreted as Latin or Unicode, or you can explicitly specify the repertoire, e.g. _latin '0A'xc or _unicode '000A'xc. Note that string literal values are always Unicode, though, even if the input repertoire is Latin. And if any of the arguments to OTRANSLATE or OREPLACE is Unicode, then any Latin arguments will be implicitly translated to Unicode. 

 

One way to avoid Unicode translation would be to use CHR (which returns LATIN) for both the second and third arguments: otranslate(load_hash_key_val,chr(10)||chr(13),chr(32))

 

The only character that fails a Latin_to_Unicode translation is the "error character", hex 1A or CHR(26), which indicates that a translation error occurred previously, e.g. prior to or during loading. If you explicitly TRANSLATE ... WITH ERROR then that character will be replaced with U+FFFD. If you opt to remove the error character also, note that U+FFFD is not considered part of the Unicode repertoire, so you can't enter it directly as a hex character literal (Latin or Unicode) or a Unicode delimited literal. But you can use CHR(26) for Latin or explicitly TRANSLATE(CHR(26) USING LATIN_TO_UNICODE WITH ERROR) for Unicode. 

 

Also, since you asked: The TRANSLATE_CHK returns the position of the first character that causes a translation error; a 0 return means there was no error.

1 ACCEPTED SOLUTION
6 REPLIES
Highlighted
Teradata Employee

Re: Unable to remove carriage returns and line feed from columns

Try using chr(11) and chr(12) instead of hex values

Enthusiast

Re: Unable to remove carriage returns and line feed from columns

Tried replacing as you said.Still no luck.

This is very frustrating.

 

PFB the command 

 

select oreplace(translate(load_hash_key_val using latin_to_unicode WITH ERROR),chr(12),' ') from table;

Teradata Employee

Re: Unable to remove carriage returns and line feed from columns

Chr() returns a Latin character, so it has no meaning with Unicode strings.  Also, hex 0A is chr(10) and hex 0D is chr(13), not 11 and 12!  Correct the chr() expression and make sure you are using Latin strings.  Or, if these are Unicode strings, then use the Unicode hex representation instead of chr(), such as '000A'x.

Enthusiast

Re: Unable to remove carriage returns and line feed from columns

@GJColeman When i try to run the oreplace command without translate like

oreplace(col_name,'000A'xc,'')

then it throws "string contains an untranslatable character".

 

So i use the translate inside replace,

oreplace(translate(load_hash_key_val using latin_to_unicode WITH ERROR),chr(12),' ')

 

But i still see the newline characters in the result set.

 

Is there any other way i can just select atleast without these characters ??

 

I did a translate_chk on that column using

select translate_chk (col_name,using latin_to_unicode)

 

I see different numbers like 0,16,18.

 

What does that imply ?

Teradata Employee

Re: Unable to remove carriage returns and line feed from columns

 

For your purposes, an OTRANSLATE (character by character replacement) seems more appropriate than OREPLACE (string search and replacement).

 

When you specify character literals in hex, it depends on your session character set as to whether the hex values are interpreted as Latin or Unicode, or you can explicitly specify the repertoire, e.g. _latin '0A'xc or _unicode '000A'xc. Note that string literal values are always Unicode, though, even if the input repertoire is Latin. And if any of the arguments to OTRANSLATE or OREPLACE is Unicode, then any Latin arguments will be implicitly translated to Unicode. 

 

One way to avoid Unicode translation would be to use CHR (which returns LATIN) for both the second and third arguments: otranslate(load_hash_key_val,chr(10)||chr(13),chr(32))

 

The only character that fails a Latin_to_Unicode translation is the "error character", hex 1A or CHR(26), which indicates that a translation error occurred previously, e.g. prior to or during loading. If you explicitly TRANSLATE ... WITH ERROR then that character will be replaced with U+FFFD. If you opt to remove the error character also, note that U+FFFD is not considered part of the Unicode repertoire, so you can't enter it directly as a hex character literal (Latin or Unicode) or a Unicode delimited literal. But you can use CHR(26) for Latin or explicitly TRANSLATE(CHR(26) USING LATIN_TO_UNICODE WITH ERROR) for Unicode. 

 

Also, since you asked: The TRANSLATE_CHK returns the position of the first character that causes a translation error; a 0 return means there was no error.

Enthusiast

Re: Unable to remove carriage returns and line feed from columns

@Fred Even though i had a workaround for it, I so wanted to make this work.
Thank you so much, legend !