Inconsistent results from Oracle's REGEXP_SUBSTR

Question

Given a string of key-value pairs: /* USER='Administrator'; UNV='Universe'; DOC='WebIntellignceReport'; */

My goal is to extract values associated with the USER, UNV, and DOC keys.

Using a pattern of (?<=UNV=')(.*?)(?='), I get the expected value of Universe associated the UNV key (Fiddle).

However, when I use the pattern with REGEXP_SUBSTR, I get a NULL:

SELECT  text
        ,REGEXP_SUBSTR(text,'(?<=UNV='')(.*?)(?='')') UNV
FROM (
  SELECT  '/* USER=''Administrator''; UNV=''Universe''; DOC=''WebIntellignceReport''; */' as text 
  FROM    dual
) v

What am I missing?

Oracle regex does not support lookarounds. Try REGEXP_SUBSTR(text,'UNV=''(.*?)''', 1, 1, NULL, 1) UNV — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Feb 7, 2018 at 14:32
You are testing these expressions using pcrp(php) regexp engine. This won't work on Oracle because it's regexp implementation is limited and doesnt support many features, see this link for details: regular-expressions.info/oracle.html — krokodilko
– krokodilko, Commented Feb 7, 2018 at 14:49
Moreover, the query you posted (EXACTLY as posted) will not even pass compilation, since you are not handling single-quotes properly in the input string. If you post something for us to test, please test it yourself first, and fix it if it needs fixing. — user5683823
– user5683823, Commented Feb 7, 2018 at 14:51
@mathguy, upon closer inspection, my IDE (Datagrip) did correct the /* ... */syntax, so it worked. Must have copied different source. — craig
– craig, Commented Feb 7, 2018 at 15:15

Wiktor Stribiżew · Accepted Answer · 2018-02-08 07:47:52Z

1

You may extract the contents of group 1:

SELECT  text, REGEXP_SUBSTR(text,'UNV=''(.*?)''', 1, 1 ,NULL, 1) UNV
FROM (
  SELECT  '/* USER=''Administrator''; UNV=''Universe''; DOC=''WebIntellignceReport''; */' as text 
  FROM    dual
) v

See the online demo.

With UNV='(.*?)' , you may extract just what is between the closest single quuotes afterUNV=.

answered Feb 8, 2018 at 7:47

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kfinity · Accepted Answer · 2018-02-07 15:16:05Z

I think the easiest thing to do is just grab the whole key-value pair using REGEXP_SUBSTR, and then do another substr to pull out the value you want.

with v as (select '/* USER=''Administrator''; UNV=''Universe''; DOC=''WebIntellignceReport''; */' as text from dual)
select text, key_val, substr(key_val, instr(key_val, '''')+1, length(key_val)-instr(key_val, '''')-2)
from (
    select text, 
    regexp_substr(text, ' UNV=''[^'']*'';') key_val 
    from v);

Output:

TEXT                                                                    KEY_VAL                                                                 VAL                                                                    
----------------------------------------------------------------------- ----------------------------------------------------------------------- -----------------------------------------------------------------------
/* USER='Administrator'; UNV='Universe'; DOC='WebIntellignceReport'; */  UNV='Universe';                                                        Universe

Collectives™ on Stack Overflow

Inconsistent results from Oracle's REGEXP_SUBSTR

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related