Re: Help: a sql question

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Thu, 15 Aug 2002 14:35:26 -0600
Message-ID: <3D5C108D.F12143AA@noaa.gov>

It's easy. You can easily translate the non-numbers to null and then compare
the length of the original string to the newly translated string. Something like this
would work:

SQL> create table test (phono_no varchar2(20));

Table created.

SQL> insert into test values ('2158983309');

1 row created.

SQL> insert into test values ('301A3458793');

1 row created.

SQL> select * from test;

PHONO_NO

---

2158983309
301A3458793

SQL> select phono_no from test where
  2 length(phono_no) !=
length(translate(upper(phono_no),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',   3 '0123456789'));

PHONO_NO

---

301A3458793

Of course, you could prevent this problem from occurring in the future by defining
your column to be a number. If you need to accept things like dashes, parentheses,
etc. then a simple check like the one above would be suitable. This would best be
done in a 'before row' trigger so that bad data wouldn't even be inserted.

Happy trails,
Tom

Joe Bayer wrote:

> 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?
>
>
>
> --
> Sent by joebayerii from hotmail within area com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Thu Aug 15 2002 - 15:35:26 CDT