Re: Help: a sql question

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Thu, 15 Aug 2002 20:58:09 -0400
Message-ID: <ulojh94q3303ce@corp.supernews.com>

Different ways to do the same thing ....

You can also do something like this:
SQL> select * from test;

PHONO_NO

---

2158983309
301A3458793
215898^309
215898*309

SQL> select phono_no from test where
  2 replace(translate(translate(phono_no,'*','^'),

                              '0123456789','**********'),'*') is not null;

PHONO_NO

---

301A3458793
215898^309
215898*309

Effectively making sure that nothing except numbers are there in the column. Though I'd admit that the query does not look pretty :)

Anurag
"Thomas Gaines" <Thomas.Gaines_at_noaa.gov> wrote in message news:3D5C108D.F12143AA_at_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 - 19:58:09 CDT