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.
toookiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
regexp_substr(f1,'^[0-9]+')
0
 
Ivo StoykovCommented:
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

0
 
toookiAuthor Commented:
Thank you all. I am checking.
0
 
sdstuberCommented:
 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
0
 
toookiAuthor Commented:
Thanks a lot. regexp_substr(f1,'^[0-9]+')  worked perfectly.
Thanks everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.