Re: SQL: Translate

In article <1992Apr15.165953.1542_at_news2.cis.umn.edu> ayers_at_shark.micro.umn.edu (Timothy R. Ayers) writes:
>
>I am trying to update an address line in the database where someone
>entered an "*" or "**" at the end of each mailbox number. So the records
>look like this: 2076**
> 1367*
>
>I would like to remove the "*" and replace it with spaces or null.
>Someone suggested using the TRANSLATE function, but I can't find
>much documentation on it. Does anyone have any suggestions how to make
>this function work.
>
>Judi

It has been a while and I don't have any docs with me, but if you want spaces try:

     TRANSLATE(mailbox,'*',' ')

If the mailbox number can vary in length and you want NULLs try:

     SUBSTR(mailbox,1,INSTR(mailbox,'*') - 1)

or

     RTRIM(TRANSLATE(mailbox,'*',' '))

If your mailbox number is always four digits long and you want NULLs try:

     SUBSTR(mailbox,1,4)

All of these commands are documented in the SQL Lanuage Reference Manual.

-- 
Michael Wang
mmwang_at_adobe.com