• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

REGEXP_SUBSTR extraction from string

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
skahlert2010
Asked:
skahlert2010
1 Solution
 
Shinesh PremrajanCommented:
select to_number(trim(regexp_substr(str_result,'[0-9,]'))) str_result

Hope this helps
0
 
sdstuberCommented:
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
 
Marco GasiFreelancerCommented:
You can try this: (\d[^,])+|(\d+,\d+)+

Cheers
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
skahlert2010Author Commented:
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
 
Marco GasiFreelancerCommented:
Glad to helped you. You find a good reference here http://regexplib.com and here: http://www.regular-expressions.info/

Cheers
0
 
skahlert2010Author Commented:
Thanks! That will be quite handy in the future!

Brgds....
0
 
Marco GasiFreelancerCommented:
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
 
skahlert2010Author Commented:
Thanks for the excellent answer!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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