Tip of the Week
Tip for Week of December 6, 2004

Find Numeric and Non-Numeric Records

This tip comes from Ilya Petrenko, Senior Oracle DBA, Open Distributed Solutions, Inc., in Jamison, Pennsylvania.

This script uses two Oracle functions—TRANSLATE and Length—to identify if data includes numeric or non-numeric characters.

create table TMP_DATA
(ROW_SEQ# NUMBER,
STRING# VARCHAR2(100)
);

INSERT INTO TMP_DATA
SELECT ROWNUM,
OBJECT_NAME
||DECODE( MOD(ROWNUM,4), 0, NULL,to_char(ROWNUM))
||OBJECT_TYPE
from user_objects
where object_name like 'T%'
UNION ALL
select 100, ' 87 ' from dual
UNION ALL
select 100, '0123' from dual
UNION ALL
select 100, '911' from dual
;

col STRING# for a60
select * from TMP_DATA;

Find ALL rows where you have numeric characters only:

select ROW_SEQ#, STRING#
from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#, CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) = 0
;


ROW_SEQ#   STRING#
---------- --------
100        0123
100        911

Find ALL rows where you have non-numeric characters only:

select ROW_SEQ#, STRING#
from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#, CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) > 0
;


ROW_SEQ#   STRING#
---------- ------------------------------------
1          T0011TABLE
2          TEST22TABLE
3          TMP_DATA3TABLE
5          TRANS5TABLE
6          TRANS216TABLE
7          TRIV7TRIGGER
100        87