I'm currently using the apex_string.split procedure to split words in a string, and it works great.
select * from table(apex_string.split('abc def xyz',' '));
abc
def
xyz
However, I'm trying to cater to the introduction of words surrounded by quotes to be treated as one entry.
Without adjustment, the split function turns this into 5 records, not 4 (with "harley davidson" as one entry)
select * from table(apex_string.split('abc "harley davidson" def xyz',' '));
abc
"harley
davidson"
def
xyz
ie - my ideal output would be this, with or without the quotes
abc
"harley davidson"
def
xyz
This seems to be a common question on developer forums, and so far I've been able to adapt a regular expression to deliver what I'm after in a two step process. I figure if I can replace any space within the quotes with an underscore, I can split the resulting string with spaces, and treat the output with some simple replaces.
with data as (select 'abc "harley davidson" def xyz' str from dual)
select str
-- https://www.codeproject.com/Articles/869215/Replace-All-Spaces-Or-Any-Other-Character-Between
,regexp_replace(str, '\"(.+?)\"','_') too_far
,regexp_substr(str, '\"(.+?)\"') yeah_but
,'abc "harley_davidson" def xyz' I_want_this
-- combine what I have to get result, seems verbose
,regexp_replace(str, '\"(.+?)\"',replace(regexp_substr(str, '\"(.+?)\"'),' ','_')) whoa
from data;
Here my WHOA result column could be treated with
select replace(replace(column_value,'_',' '),'"') column_value
from table(apex_string.split('abc "harley_davidson" def',' '));
abc
harley davidson
def
xyz
Combined, this could look like
select replace(replace(column_value,'_',' '),'"') column_value
from table(apex_string.split(
regexp_replace('abc "harley_davidson" def xyz'
, '\"(.+?)\"'
,replace(regexp_substr('abc "harley_davidson" def xyz', '\"(.+?)\"'),' ','_'))
,' '));
But given the apex_string.split function will accept a regular expression, I can't help but feel this is really close to the answer I'm chasing
select column_value res
from table(apex_string.split('abc "harley davidson" def xyz','\"(.+?)\"'));
abc
def xyz
But the result isn't quite right. I'm sure an educated adjustment to the expression would deliver the results I'm chasing.
So
Can I introduce a regular expression to apex_string.split to complete this task? or
Is there a (neater) regexp_replace expression that could replace any spaces within a quoted pair with another character?
This is currently on an 11g database.
Scott