Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help: a sql question

Re: Help: a sql question

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 16 Aug 2002 10:53:33 -0700
Message-ID: <336da121.0208160953.36b53443@posting.google.com>


u705413818_at_spawnkill.ip-mobilphone.net (Joe Bayer) wrote in message news:<l.1029440386.1122680664@[64.94.198.252]>...
> Guy,
>
> We have a table, col1 is varchar2
>
> SQL> select * from test;
>
> phone_no
> ----------
> 2158983309
> 4125689834
> 301A3458793
> 718834T4325
>
>
> How can I find those bad records which contains non unmber?

select phone_no
from test
where replace(translate(phone_no, '123456789', 000000000'), '0', '')

       is not null

Now, people might start telling you (and me, of course!) that null is not a zero length string. Oracle declared that in future versions zero length varchar2 variable might be different from null. But in all current versions zero length varchar2 is null. Moreover, zero length varchar2 doesn't behave like varchar2, it behaves like null. For example, '' = '' returns false.

Anyway, if you what bulletproff solution, where empty string is not necessary null, here it is:

select phone_no
from test
where length(replace(translate(phone_no, '123456789', 000000000'), '0', '')) != 0 Received on Fri Aug 16 2002 - 12:53:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US