Solved

REGEXP_SUBSTR extraction from string

Posted on 2010-11-08
8
1,195 Views
Last Modified: 2012-05-10
Dear experts and regular expression wizards,

I need to extract numbers with European comma seperation from a string.
For instance 4,32.

Sometimes, however,  I will have a single integer such as 4 embedded in a string only.
I tried the regular expression below, which only works for  comma separarated values. Can you please show me how to extract single numbers as well as.

To be precise I need a regular expression that extracts 4 and 4,32 from a string such as  "The result values are 4 and 4,32! Recommended task..."
select to_number(trim(regexp_substr(str_result,'[0-9][,][0-9]'))) str_result, decode((str_bewertung),'A+',9,'A',8,'A-',7,'B+',6,'B',5,'B-',4,'C+',3,'C',2,'C-',1) as bewertungspunkte, str_bewertung str_bewertung from (select str_typ, str_result, str_bewertung from tbl_matrix_result where LNG_GEBIET = :xp_cnt_gebiet and STR_LRT = :xp_str_lrt and INT_BE = :xp_be and INT_WG = :xp_wg and EVAL_TYPE = 4 and lower(str_typ) = 'biotopbaum' );

Open in new window

0
Comment
Question by:skahlert2010
8 Comments
 
LVL 17

Expert Comment

by:shinuq
Comment Utility
select to_number(trim(regexp_substr(str_result,'[0-9,]'))) str_result

Hope this helps
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if the string has more than one number in it,  what are you expecting the results to be?  One row per value?  or one column per value?  The first is possible,  the second is not.
Is there a maximum number of numbers you might expect? Will it be only 1 or 2 or could there be many?
0
 
LVL 30

Accepted Solution

by:
Marco Gasi earned 500 total points
Comment Utility
You can try this: (\d[^,])+|(\d+,\d+)+

Cheers
0
 

Author Comment

by:skahlert2010
Comment Utility
Thanks marqusG!

Brilliant! I's working great! Do you guys have a reference for regular expressions?
I am sometimes having trouble to build my own function successfully since I am exactly not fond of the parameters.

Bye!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
Glad to helped you. You find a good reference here http://regexplib.com and here: http://www.regular-expressions.info/

Cheers
0
 

Author Comment

by:skahlert2010
Comment Utility
Thanks! That will be quite handy in the future!

Brgds....
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
For some bug, link to accept answer and awrad points is missing. Please, skahlert2010, click Request attention link to ask a moderator to award points to my answer and close question.

Thanks and good bye

Marco
0
 

Author Closing Comment

by:skahlert2010
Comment Utility
Thanks for the excellent answer!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

7 Experts available now in Live!

Get 1:1 Help Now