CHR(26) replacement in teradata

Database
Enthusiast

CHR(26) replacement in teradata

Hi,

I've searched through lots of posts but didn't find what is the replacement for CHR(26) in teradata. 

We want to replace cha(26) with null value : replace(a, chr(26), null)

Thanks in advance. 

5 REPLIES
Teradata Employee

Re: CHR(26) replacement in teradata

You need to use the oreplace, or translate functions. Refer to the SQL functions documentation. If it is failing with a 6706, this is because the 0x1A (26 dec) is the ASCII error character.

 BTEQ -- Enter your SQL request or BTEQ command:

sel chr(26);

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

chr(26)

-------

 BTEQ -- Enter your SQL request or BTEQ command:

sel type(chr(26));

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

Type(chr(26))

---------------------------------------

CHAR(1)

 BTEQ -- Enter your SQL request or BTEQ command:

sel char2hexint(chr(26));

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

Char2HexInt(chr(26))

--------------------

1A

 BTEQ -- Enter your SQL request or BTEQ command:

sel oreplace('a', chr(26),null);

 *** Failure 6706 The string contains an untranslatable character.

-Davd Craig

Enthusiast

Re: CHR(26) replacement in teradata

Thank you David, it helps. 

Enthusiast

Re: CHR(26) replacement in teradata

SyntaxEditor Code Snippet

sel TRANSLATE ('dj vu - DVD',CHR(26),null); is not working getting 6706 error.
Highlighted
Teradata Employee

Re: CHR(26) replacement in teradata

A string literal is always UNICODE so the database is trying to translate the other arguments to UNICODE for compatibility.

Also, if the third argument to OTRANSLATE is NULL, the result will be NULL. I think you mean to have an empty string here. (OREPLACE would accept a NULL or empty string, or you could just omit the third argument.)

If you use a LATIN column or explicitly make the argument values LATIN, it should work:

 

select OTRANSLATE(translate('dj vu - DVD' using Unicode_to_Latin),CHR(26),translate('' using Unicode_to_Latin));
select OREPLACE(translate('dj vu - DVD' using Unicode_to_Latin),CHR(26));
Enthusiast

Re: CHR(26) replacement in teradata

Thanks for your prompt response. But still no luck on this query.

BTW I got the solution as (OREPLACE(Latin_column_name, CHR(26),CHR(32))).