Re: Q: sql to strip off special character ?

From: <lderani_at_my-deja.com>
Date: Thu, 06 Jan 2000 21:34:12 GMT
Message-ID: <8531ok$fk5$1@nnrp1.deja.com>

Try use the TRANSLATE function. For example:

select translate('Testing!the_at_translate$function now','!@#$%',' ') from dual;

will return:

TRANSLATE('TESTING!THE_at_TRANSLATE

---

Testing thetranslatefunction now

The trick is, try to include in the second parameter everything you think will appear in your phone field that you donīt want - and it will be replaced by the corresponding character at the same position in the third parameter. In my example, all '!' would be replaced by spaces, and the rest will simple disappear from the string. You can then use something with to_number to get your phone number clean.

Luis Derani (lderani_at_uol.com.br)

---

Oracle Developer
Universo Online
Brazil

In article <s74pjda55k255_at_corp.supernews.com>,   RC <rclarence_at_tyc.com> wrote:
>
> tedchyn wrote:
> >
> >
> > sir, I have a free text phone number. for example
> >
> > 1(933)-777-9999
> > 933-777-9999
> > #933-777-9999
> >
> > there is no way of predicting what special charcter may present. how
> > do I strip off special charcter either with sql update or plsql
function
> > beside
> > 1. using nested replace function(which requires you know the special
> > character ahead of time) or
> > 2. plsql block - fetch into a variable and use a for loop to take
care
> > all special charater(s).
> >
> > Thanks in advance
> > ted chyn
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> You could loop through the string in a PL/SQL function and check that
each
> character is in the set 0-9. If it is keep it if not remove it from
the
> string. You could use the INSTR function for the parsing.
>
> HTH
>
> RC
>
> --
> Posted via CNET Help.com
> http://www.help.com/
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 06 2000 - 15:34:12 CST