Identify non-numeric [message #266737] |
Tue, 11 September 2007 09:25  |
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   |
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 #266746 is a reply to message #266737] |
Tue, 11 September 2007 09:38   |
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   |
Frank
Messages: 7901 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 #266760 is a reply to message #266754] |
Tue, 11 September 2007 10:06   |
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  |
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 ?
|
|
|