Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1622
  • Last Modified:

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.
0
toooki
Asked:
toooki
  • 2
  • 2
1 Solution
 
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
 
sdstuberCommented:
regexp_substr(f1,'^[0-9]+')
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now