Oracle Regex to extract substring between two patterns

bmsande
bmsande used Ask the Experts™
on
I have a column where I need to extract a string that sits between two patterns.  The string is as follows:

A<1,?,'providerInfo'='0003\\370\\3370288.dat','storageProviderName'='ExtShared','subProviderName'='Default'>

Open in new window


And I need to extract the embedded file path

0003\\370\\3370288.dat

Open in new window


Bonus points if you can also replace the \\ with \
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I went the extra mile and looked for the delimiter providerInfo

If you have 11gR2 or above try this:
with mydata as(
select q'[A<1,?,'providerInfo'='0003\\370\\3370288.dat','storageProviderName'='ExtShared','subProviderName'='Default'>]' mycol from dual
)
select replace(regexp_substr(mycol,'(providerInfo''=''([^'']+))',1,1,null,2),'\\','\') from mydata
/

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If you don't care about the delimiter and only want that specific pattern then try this:
with mydata as(
select q'[A<1,?,'providerInfo'='0003\\370\\3370288.dat','storageProviderName'='ExtShared','subProviderName'='Default'>]' mycol from dual
)
select replace(regexp_substr(mycol,'[0-9\]+\.[a-z]{3}'),'\\','\') from mydata
/

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
SELECT REPLACE(
           REGEXP_SUBSTR(
               yourstring,
               q'['providerInfo'='(.*)','storageProviderName]',
               1,
               1,
               NULL,
               1
           ),
           '\\',
           '\'
       )
  FROM (SELECT q'[A<1,?,'providerInfo'='0003\\370\\3370288.dat','storageProviderName'='ExtShared','subProviderName'='Default'>]'
                   yourstring
          FROM DUAL)

Author

Commented:
Worked beautifully.  I award you 1 trillion bonus points.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial