TAGS :Viewed: 6 - Published at: a few seconds ago

[ Query to replace a string with matching pattern ]

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.

Answer 1


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;