oTranslate issue

Database

oTranslate issue

Hi, I have a field called TRADE_NAME which is defined as CHAR(70).  There are some weird characters in it and I want to convert them to spaces. I tried using the oTranslate below but the results show it converting commas to spaces even though I have the comma in my list. What am I doing wrong?


SEL  OTRANSLATE(TRADE_NAME,                               

        OTRANSLATE(TRADE_NAME,                               

         ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"''.<|\}{=+-_)(@&',''), ' ') 

FROM MY_VIEW.SGFDXYZ

Thanks, Dave

P.S. What does oTranslate stand for? does it mean Omit from Translating the characters in my list? Just a guess...

1 REPLY
Junior Contributor

Re: oTranslate issue

Your query will remove the characters not in the list, only the first character (a space) will be replaced by a space. 

As oTranslate replaces each character in the 2nd parameter with the character from the 3rd parameter at the same position, e.g.

oTranslate('b3572', '12345', 'abcde') results in 'bce7b'

If the length of the 3rd parameter is shorter than the 2nd those characters are removed.

You could use a long string of spaces '                                ', but it's easier to switch to a Regular Expression. This will replace all characters not in the list with spaces:

REGEXP_REPLACE(TRADE_NAME, '[^ a-z0-9"''.<|\}{=+\-_)(@&]' ,' ', 1,0 ,'i')

Caution: '\', '-', '^', ']' are meta-characters, which must be escaped: '\', '\-', '\^', '\]'

I always assumed that the 'O' stand for 'Oracle compatible'.

oTranslate implements a function which most DBMSes (e.g. Oracle, DB2, PostgreSQL) named Translate. But there is already another function in Teradata with that name (for changing the  character set of a column), thus they needed a new name.