Search code examples
regexoraclepass-through

REGEXP Help - Oracle Pass Through Query

Currently working in Microsoft Access, writing a PTQ to an Oracle Data Warehouse.

One of the fields is a description field which holds alphanumeric strings. Sometimes all characters, sometimes the inclusion of a 9 digit number. What I want to be able to do is if there is a 9 digit number, to select it from that description field and create a new field with it.

SELECT description
     REGEXP_SUBSTR( * here goes the reg exp * ) "REGEXPR_SUBSTR"
FROM myTable

REGEXP_SUBSTR
Solution 
  • select * from
(
SELECT  REGEXP_SUBSTR("desc",'\d{9}') REGEXPR_SUBSTR FROM temp1 
)
where REGEXPR_SUBSTR is not null;

    Thil will work perfectly. It rejects nulls and only accept 9 digits. Last answer I was writing in hurry. Mi scuzi :)