I have a string like below.
'comp' as "COMPUTER",'ms' as "MOUSE" ,'keybr' as "KEYBOARD",'MONT' as "MONITOR",
Is it possible to write a query so that i will get the result as
'comp' ,'ms' ,'keybr' ,'MONT' ,
I can replace the string "as" with empty string using REPLACE query. But how do I remove the string inside double quote?
Can anyone help me doing this? Thanks in advance.
select replace(
regexp_replace('''comp'' as "COMPUTER"'
, '(".*")'
,null)
,' as '
,null)
from dual
The regex '(".*")' selects text whatever in double quotes.
EDIT:
the regex replaced the entire length of the matching pattern. So, we might need to tokenise the string first using comma as delimiter and apply the regex. Later join it.(LISTAGG)
WITH str_tab(str1, rn) AS
(SELECT regexp_substr(str, '[^,]+', 1, LEVEL), -- delimts
LEVEL
FROM (SELECT '''comp'' as "computer",''comp'' as "computer"' str
FROM dual) tab
CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ',')) + 1)
SELECT listagg(replace(
regexp_replace(str1
, '(".*")'
,null)
,' as '
,null), ',') WITHIN GROUP (ORDER BY rn) AS new_text
FROM str_tab;
EDIT2:
A cleaner approach from @EatAPeach
with x(y) as (
select q'<'comp' as "COMPUTER",'ms' as "MOUSE" ,'keybr' as "KEYBOARD",'MONT' as "MONITOR",>'
from dual
)
select y,
regexp_replace(y, 'as ".*?"' ,null)
from x;
