[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

REGEXP_SUBSTR extraction from string

Posted on 2010-11-08
8
Medium Priority
?
1,252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 17

Expert Comment

by:Shinesh Premrajan
ID: 34083552
select to_number(trim(regexp_substr(str_result,'[0-9,]'))) str_result

Hope this helps
0
 
LVL 74

Expert Comment

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

Accepted Solution

by:
Marco Gasi earned 2000 total points
ID: 34083736
You can try this: (\d[^,])+|(\d+,\d+)+

Cheers
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:skahlert2010
ID: 34083771
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
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 34083794
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
ID: 34084185
Thanks! That will be quite handy in the future!

Brgds....
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 34085305
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
ID: 34086427
Thanks for the excellent answer!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

656 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