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;
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;
ASKER
no, I want two rows returned.
string one
string two
string one
string two
search EE for str2tbl
select * from table(str2tbl('string one,string two'))
select * from table(str2tbl('string one,string two'))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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) ) .....
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;
select regexp_replace('string one, string two',', *',chr(10)) from dual;
select replace('string one, string two', ',') from dual;