CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Auguster
Auguster

Reputation: 375

Ignore all numeric in REGEXP_LIKE

I am trying to find all records which have anything other than numeric value this includes alphabets charachters. I need this information to determine what datatype I am going to store the data I get from SUBSTR(COLUMN1,8,4) as I am running the following query:

select SUBSTR(COLUMN1,8,4)
from tmp_table
where REGEXP_LIKE (SUBSTR(COLUMN1,8,4),'[^0-9\x]+$');

The results I get for query are as follows:

918 
922S
944 
950N
953 
958S
974 
977A
994A
995

I am also adding the query to show input data the query used is:

select column1
from tmp_xsttable
where REGEXP_LIKE (SUBSTR(COLUMN1,24,4),'[^0-9\x]+$');

The data I got is :

AHGLXST912 200701010000912 L000000000000000000NC0000  CA20070216 10     
AHGLXST917 200501020000917 L000000000020071231NC000001CC20090805 10     
AHGLXST918 200501020000918 L000000000020071231NC000001CC20090805 10     
AHGLXST920A200501020000920AL000000000000000000NC000001CA20050130 64     
AHGLXST921 193501010000921 L000000000000000000NH0000  DA20040901 30     
AHGLXST921D193501010000921DL000000000000000000NH0000  DA20040901 30     
AHGLXST922A192501010000922AL000000000000000000NC0000  CA20050311 64     
AHGLXST922G192501010000922GL000000000020081231NC0000  CC20090805 44     
AHGLXST922N200501020000922NL000000000000000000NC0000  CA20050517 74     
AHGLXST922S193501010000922SL000000000000000000NH0000  CA20040901 10

Here are some example dataset that is in Column1 of tmp_table:

AHGLXST01362007061700530136L000000057000000000YH0005  RA20070619110     
AHGLXST01362011092500540136L000000057000000000YH0005  RA20110927110     
AHGLXST01362014090700950136L000000057000000000YC0009  RA20140909110     
AHGLXST01371973112800670137L000000026000000000YH0008  RC2004052011011   
AHGLXST01372006010100640137L000000026020061005YC0008  RC2006102511011   
AHGLXST01391988040600510139L000000080500000000YH0006  RC2004052021022   
AHGLXST01392007061700550139L000000080520101113YC0005  RC20101214210     
AHGLXST01401997071300560140L000000047400000000YH0006  RC2004052011011   
AHGLXST01402006042900560140L000000047400000000YH0008  RA20060426110     
AHGLXST01402007061700750140L000000047400000000YH0007  RA20070619110

I am trying to ignore the records which are numeric but I get these 3 digit results along with the records which have alphabets and characters. How do I change my REGEXP_LIKE to ignore all numeric?

Upvotes: 0

Views: 198

Answers (1)

David Faber
David Faber

Reputation: 12485

What you're doing in your query is getting data where SUBSTR(COLUMN1,8,4) contains at least one non-numeric character. I think what you want is more like this:

SELECT SUBSTR(column1, 8, 4)
  FROM tmp_table
 WHERE NOT REGEXP_LIKE(SUBSTR(column1, 8, 4), '\d');

The above query would return all rows for which the value of COLUMN1 does not contain a digit in the four characters starting from the 8th position (that is, characters 8-11).

Upvotes: 1

Related Questions