Link to home
Start Free TrialLog in
Avatar of claghorn
claghorn

asked on

regexp_substr to get multiple strings from one

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;
Avatar of paquicuba
paquicuba
Flag of United States of America image

Are you trying to get rid of the comma, just replace it:

select replace('string one, string two', ',') from dual;
Avatar of claghorn
claghorn

ASKER

no, I want two rows returned.
string one
string two
Avatar of Sean Stuber
search EE for str2tbl


select * from table(str2tbl('string one,string two'))
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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) ) .....
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;