oreplace of ->

Database
Enthusiast

oreplace of ->

Hi,

We have an attribute containing special character shown as below, in sql assistant(TD 14) it's showing as 1st line, but when exporting to excel it's showing as 2nd line. 

After trying OREPLACE function with below, still it's not removing that special character, could you please give some suggestions?

9 REPLIES
Enthusiast

Re: oreplace of ->

hi ,

Do you mean  in SQL assistant Oreplace is unable to remove the special symbol ?

please check one of the below option that might be help you .

SELECT REGEXP_REPLACE ('employees ->retirment ->system' ,'[-><]','',1,0,'i') 
or
sel otranslate ('employees ->retirment ->system' ,'->','');
or
sel oreplace ('employees ->retirment ->system' ,'->','');

all the function working fine to me.

please let me know  if  i don't get you correctly.

Br,

Arun

Enthusiast

Re: oreplace of ->

Hi Arun,

Thanks for the input. But that special character is not simply '->', it's only showing when exported to excel. I've tried to use oreplace of '0D'XC, '09'XC, '1C'XC,  X'0A', '0D0A'XC, '0A0D'xc, '0A'XC.  But no luck.

Teradata Employee

Re: oreplace of ->

Use CHAR2HEXINT function to determine the actual character value.

Enthusiast

Re: oreplace of ->

Hi memostone

What is the characterset defined for the attribute? latin or unicode?

I tried the below which I coundn't see anything in the SQLA 14.10 but when I saved in the excel, it showed this arrow symbol(attached)

sel U&'#0362' uescape '#';

Even I tried to replace/traslate, it gives me Failed. 6706:  The string contains an untranslatable character. error.

Could you let us know how this untranslatable character is added in the attribute? During import or export?

Thanks,

Dinesh

Enthusiast

Re: oreplace of ->

@dinesh, it cannot be translated, we got data from a different resource, sorry i'm not sure how it got loaded.

After CHAR2HEXINT function, it's showing "1A", a unrecognizable character in teradata.

Enthusiast

Re: oreplace of ->

Memostone,

Ok. If thats the case, you can replace the unrecognizable character in the excel sheet. You will get lots of excel macros by googling or you can modify it manually. 

Please share if you have any ways to traslate it from DB side. I hope there should be a way for this :)

Thanks,

Dinesh

Teradata Employee

Re: oreplace of ->

The character arguments to the functions must all be LATIN to avoid the implicit translation of all character values to Unicode (which will fail with 6706); note that literals are Unicode.

OREPLACE(latinColumn,CHR(26),CHR(32)) /* replace with a space */

OREPLACE(latinColumn,CHR(26),translate('' using Unicode_to_Latin)) /* replace with empty string, i.e. remove */

Enthusiast

Re: oreplace of ->

Hi Fred,

CHR(26) works, thank you!

Enthusiast

Re: oreplace of ->

This works as well to find a replace the hex identified values in a givien field.

SyntaxEditor Code Snippet

(oreplace( OREPLACE(char2hexint(rp.inputcolumn) , 'AD',''), '2D',''))