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?
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')
sel otranslate ('employees ->retirment ->system' ,'->','');
sel oreplace ('employees ->retirment ->system' ,'->','');
all the function working fine to me.
please let me know if i don't get you correctly.
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.
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?
@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.
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 :)
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 */
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',''))