Oracle Regex to extract substring between two patterns
I have a column where I need to extract a string that sits between two patterns. The string is as follows:
And I need to extract the embedded file path
Bonus points if you can also replace the \\ with \
A<1,?,'providerInfo'='0003\\370\\3370288.dat','storageProviderName'='ExtShared','subProviderName'='Default'>
And I need to extract the embedded file path
0003\\370\\3370288.dat
Bonus points if you can also replace the \\ with \
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I went the extra mile and looked for the delimiter providerInfo
If you have 11gR2 or above try this:
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
/
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
/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial