I need to parse each quoted value for the '@name' keys in a string. The string can have a variable number of occurrences of this pair. I've tried a couple of things but they either get too bulky, so to speak, or they are beyond my knowledge level.

This is using Oracle SQL.

Any help is appreciated.

Here's a few examples:

/content/folder[@name='LocationB']/report[@name='Report29']

/content/folder[@name='LocationA']/folder[@name='Reports']/report[@name='ReportAAA']

/content/folder[@name='LocationA']/folder[@name='Reports']/folder[@name='FolderC']/folder[@name='FolderCD']/report[@name='ReportBBB']

/transientStateFolder/agentState[@name='2019-05-09T19:47:03.029Z']/reportView[@name='Report232']

CAMID("corp.xyz.net:u:254679b8195bf547a8b894de8a6ea822")/folder[@name='My Folders']/folder[@name='Subscriptions']/reportView[@name='My Daily Numbers']

I hope to end up with:

LocationB Report29

LocationA Reports ReportAAA

LocationA Reports FolderC FolderCD ReportBBB

etc...

CodePudding user response：

If you don't have/know/want regexp - you can use SubStr() and InStr() functions:

WITH
    tbl AS
        (
            Select 1 "ID", '/content/folder[@name="LocationB"]/report[@name="Report29"]' "MY_STR" From Dual Union All
            Select 2 "ID", '/content/folder[@name="LocationA"]/folder[@name="Reports"]/report[@name="ReportAAA"]' "MY_STR" From Dual Union All
            Select 3 "ID", '/content/folder[@name="LocationA"]/folder[@name="Reports"]/folder[@name="FolderC"]/folder[@name="FolderCD"]/report[@name="ReportBBB"]' "MY_STR" From Dual Union All
            Select 4 "ID", '/transientStateFolder/agentState[@name="2019-05-09T19:47:03.029Z"]/reportView[@name="Report232"]' "MY_STR" From Dual Union All
            Select 5 "ID", 'CAMID("corp.xyz.net:u:254679b8195bf547a8b894de8a6ea822")/folder[@name="My Folders"]/folder[@name="Subscriptions"]/reportView[@name="My Daily Numbers"]' "MY_STR" From Dual
        )
            
        
Select
    ID "ID",
    RTRIM(
          SubStr(MY_STR, InStr(MY_STR, '[@name="', 1, 1)   8, InStr(MY_STR, '"]', 1, 1) - InStr(MY_STR, '[@name="', 1, 1) - 8) || ' ' || 
          SubStr(MY_STR, InStr(MY_STR, '[@name="', 1, 2)   8, InStr(MY_STR, '"]', 1, 2) - InStr(MY_STR, '[@name="', 1, 2) - 8) || ' ' ||
          SubStr(MY_STR, InStr(MY_STR, '[@name="', 1, 3)   8, InStr(MY_STR, '"]', 1, 3) - InStr(MY_STR, '[@name="', 1, 3) - 8) || ' ' ||
          SubStr(MY_STR, InStr(MY_STR, '[@name="', 1, 4)   8, InStr(MY_STR, '"]', 1, 4) - InStr(MY_STR, '[@name="', 1, 4) - 8) || ' ' ||
          SubStr(MY_STR, InStr(MY_STR, '[@name="', 1, 5)   8, InStr(MY_STR, '"]', 1, 5) - InStr(MY_STR, '[@name="', 1, 5) - 8)
         ) "MY_STR"
From
    tbl

... resulting as ...

Regards...