Special Characters

Database
Enthusiast

Special Characters

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,

NEW YORK,

NY 1

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

00340035003200200035005400480020004100560045004E00550045002C0020000D000A004E0045005700200059004F0052004B002C0020000D000A004E0059002000310020

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.

Thanks

John

4 REPLIES
Senior Apprentice

Re: Special Characters

Hi John, 

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.

Enthusiast

Re: Special Characters

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

Thanks

john

Senior Apprentice

Re: Special Characters

Hi John,

of course there is, it's called UPDATE :-)

update tab
set col = oTranslate (col, '0D0A'xc,'')
where col <> oTranslate (col, '0D0A'xc,'')
Enthusiast

Re: Special Characters

Thank you very much dieter....It worked perfect.