Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help: a sql question
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