Thursday, 10 December 2015

Regular Expression - Data Enclosed within double quotes

With A As
  (Select 'when "EDW"."Dim - Store Operations Metrics"."Attribute Value"  = ''StaffHours''  then ''18016'' '''  As Text From Dual),
b as (
SELECT 
    Text, 
    REGEXP_SUBSTR(text, '("[[:print:]]*"\.)+"[[:print:]]*"',1,level) part, 
    level l
  FROM a
  Connect By 
    REGEXP_SUBSTR(text, '("[[:print:]]*"\.)+"[[:print:]]*"',1,level) is not null
  )
Select Text,Listagg(B.Part,' ') Within Group (Order By L)
from b group by text;   

No comments:

Post a Comment