Re: sql to strip off special character ?

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 5 Jan 2000 15:14:50 GMT
Message-ID: <84vn5a$es0$3@news.seed.net.tw>

<tedchyn_at_yahoo.com> wrote in message news:84t2i3$69h$1_at_nnrp1.deja.com...
> 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).

Use tralsnate() function twice:

select to_number(translate(no,
'0'||translate(no, ' 0123456789', ' '), '0')) as no from .... Received on Wed Jan 05 2000 - 09:14:50 CST