asked on

Oracle 11g REGEXP_SUBSTR question

I have a field (varchar2 type) in oracle table with sample contents:
f1
---
33345
223
230.0
234 567
abc345
34902,345
2
Country of
3345-45

I want an O/P:
33345
223
230
234
NULL -- For abc345
34902
2
NULL -- Country of
3345

-----
So I want to extract the first numeric part of the string (of any length) . If the string does not start with number (0-9), return null. I tried with REGEXP_SUBSTR('my string', ....) --- need some help.

Ivo Stoykov

Hi this the code below. table in mine select is named t

HTH

Ivo Stoykov

select f1, case when regexp_like(f1, '[a-zA-Z]') then null else f1 end case from t t

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toooki

ASKER

Thank you all. I am checking.

Sean Stuber

34913882 doesn't produce the requested results

here's the test case I used

WITH yourtable
AS (SELECT '33345' f1 FROM DUAL
UNION ALL
SELECT '223' FROM DUAL
UNION ALL
SELECT '230.0' FROM DUAL
UNION ALL
SELECT '234 567' FROM DUAL
UNION ALL
SELECT 'abc345' FROM DUAL
UNION ALL
SELECT '34902,345' FROM DUAL
UNION ALL
SELECT '2' FROM DUAL
UNION ALL
SELECT 'Country of' FROM DUAL
UNION ALL
SELECT '3345-45' FROM DUAL)
SELECT f1,
CASE WHEN REGEXP_LIKE(f1, '[a-zA-Z]') THEN NULL ELSE f1 END bad,
REGEXP_SUBSTR(f1, '^[0-9]+') good
FROM yourtable

toooki

ASKER

Thanks a lot. regexp_substr(f1,'^[0-9]+') worked perfectly.
Thanks everyone.