?
Solved

regexp_substr to get multiple strings from one

Posted on 2013-01-25
7
Medium Priority
?
1,345 Views
Last Modified: 2013-01-25
I've been trying to get both 'string one' and 'string two' returned to me from this query.
Can it be done?
I can get one or the other but not both.
select regexp_substr('string one, string two', '[^,]+',1,1) from dual;
0
Comment
Question by:claghorn
[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
  • 4
  • 2
7 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 38819145
Are you trying to get rid of the comma, just replace it:

select replace('string one, string two', ',') from dual;
0
 

Author Comment

by:claghorn
ID: 38819156
no, I want two rows returned.
string one
string two
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38819173
search EE for str2tbl


select * from table(str2tbl('string one,string two'))
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 38819181
or using built ins

SELECT     REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL)
      FROM (SELECT 'string one, string two' s FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(s, '[^,]+')


or, if you know you'll always have 2 values

SELECT     REGEXP_SUBSTR('string one, string two', '[^,]+', 1, LEVEL)
      FROM DUAL
CONNECT BY LEVEL <= 2;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 800 total points
ID: 38819197
another method if you don't have regexp_count but don't know how many elements there will lbe


SELECT     REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL)
      FROM (SELECT 'string one, string two' s FROM DUAL)
CONNECT BY LEVEL <= LENGTH(s) - LENGTH(REPLACE(s, ',')) + 1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38819206
you may want to put a trim around the results if you have extra spaces around the comma delimiters


select  TRIM( REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL) ) .....
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38819225
Just in case what you want are two rows but within the same string, do this:

select regexp_replace('string one, string two',', *',chr(10)) from dual;
0

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

752 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