Hi All -
Can you please help me to resolve the below problem.
I have a table with address column and output of that column in SQL Assistant answer set is as below (not in a singel line)
000 6TH AVENUE,
When i am trying to do 'CHAR2HEXINT(ADDRESS_COL)' on that column i am getting some special characters in hex '000D000A'. It appears twice. One set appears just before 'NEW YORK,', another set appears just before 'NY 1'.
below is the complete out put
Could you please help me how to remove these special charatcers i used OTRANSLATE(UPPER(CHAR2HEXINT(ADDR_LINE_1_TEXT)), '000D000A','') but it is also removing other characters.
CHAR2HEXINT returns a string of digits, so you can't use oTranslate.
'0D0A' is a Windows carriage return/linebreak combination, simply use oTranslate(col, '0D0A', '') to remove both chars independently or oReplace(col, '0D0A', '') to remove only this specific combination.
Hi Dieter -
Thanks for the solution since the carriage return is already in the column value is there any function in teradata to use and delete/update those carriage return values from the table data
of course there is, it's called UPDATE :-)
set col = oTranslate (col, '0D0A'xc,'')
where col <> oTranslate (col, '0D0A'xc,'')