Find Numeric and Non-Numeric Records

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