Solved

regexp_substr to get multiple strings from one

Posted on 2013-01-25
7
1,299 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 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 200 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

789 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