Home » SQL & PL/SQL » SQL & PL/SQL » Identify non-numeric
Identify non-numeric [message #266737] Tue, 11 September 2007 09:25 Go to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
Hi... I'm trying to identify every row that has non numeric data in it.

I thought the following sql would work, but of course it doesn't.

delete from newtable where
UPPER(datafield) like '%ABCDEFGHIJKLMNOPQRSTUVWXYZ%';

I also would like to include other weird characters in the clause including % and single and double quotes.

Can someone help me fix the sql?

I would appreciate it...
Re: Identify non-numeric [message #266740 is a reply to message #266737] Tue, 11 September 2007 09:29 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What database version ?

If 10g or higher, look up reg expressions. If 9i or lower look up the TRANSLATE function.

Re: Identify non-numeric [message #266741 is a reply to message #266737] Tue, 11 September 2007 09:31 Go to previous messageGo to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
Thanks... It would be 9i.
Re: Identify non-numeric [message #266746 is a reply to message #266737] Tue, 11 September 2007 09:38 Go to previous messageGo to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
I've been looking at translate - but I must be missing something... I don't want to do a select and replace, like the following:

SELECT TRANSLATE(UPPER(datafield), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '|')
FROM table;

I don't want to do a replacement - I just want to construct a where clause and see all of the records that have alpha characters...
Re: Identify non-numeric [message #266752 is a reply to message #266746] Tue, 11 September 2007 09:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you combine translate and length, you can compare the length of your column with the length of the column-value after translate. You can use that in your where clause and either define the two must be equal or non-equal.
Re: Identify non-numeric [message #266754 is a reply to message #266752] Tue, 11 September 2007 09:52 Go to previous messageGo to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
Cool. Got it... Thanks.
Re: Identify non-numeric [message #266760 is a reply to message #266754] Tue, 11 September 2007 10:06 Go to previous messageGo to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
Well, I thought I got it... It appears that it is not finding all rows that have any of the following characters..

The statement is executing strange results; for example if I add XYZ, then I return less rows...

I'm not understanding the problem - can you shed light on it? or modify the query so that it will work correctly?

select *
from table
where length(TRANSLATE(upper(field),'ABCDEFGHIJKLMNOPQRSTUVW*[]+','|'))=1 ;
Re: Identify non-numeric [message #266763 is a reply to message #266737] Tue, 11 September 2007 10:21 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
 
SQL> create table translate_test (col1 varchar2(10));

Table created

SQL> insert into translate_test (col1) values ('ABC');

1 row inserted

SQL> insert into translate_test(col1) values ('AB1');

1 row inserted


SQL> select * from translate_test;

COL1
----------
ABC
AB1

SQL> select TRANSLATE(upper(col1),'ABCDEFGHIJKLMNOPQRSTUVW*[]+','|') from translate_test;

TRANSLATE(UPPER(COL1),'ABCDEFG
------------------------------
|
|1

SQL> select col1 from translate_test where length(TRANSLATE(upper(col1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ*[]+','|')) =1;

COL1
----------
ABC


Can you cut and paste your SQL ?
Previous Topic: Aggregate Count From Two Tables
Next Topic: Bind variables with DBMS_XMLGEN
Goto Forum:
  


Current Time: Sat Dec 10 08:50:15 CST 2016

Total time taken to generate the page: 0.08903 seconds