Solved

Oracle 11g REGEXP_SUBSTR question

Posted on 2011-02-16
5
1,588 Views
Last Modified: 2012-08-13
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
Comment
Question by:toooki
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 34913882
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 450 total points
ID: 34915416
regexp_substr(f1,'^[0-9]+')
0
 

Author Comment

by:toooki
ID: 34919629
Thank you all. I am checking.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34921329
 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
 

Author Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MySQL 11 68
T-SQL Convert to PL/SQL 23 62
Exchange 2010 Mailbox Database offline 2 59
VB.Net - CSV to Oracle table 4 29
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now