Lets say I have following string: 'product=1627;color=45;size=7' in some field of the table. I want to query for the color and get 45.
With this query:
SELECT REGEXP_SUBSTR('product=1627;color=45;size=7', 'color\=([^;]+);?') "colorID"
FROM DUAL;
I get :
colorID
---------
color=45;
1 row selected
.
Is it possible to get part of the matched string - 45 for this example?
One way to do it is with REGEXP_REPLACE. You need to define the whole string as a regex pattern and then use just the element you want as the replace string. In this example the ColorID is the third pattern in the entire string
SELECT REGEXP_REPLACE('product=1627;color=45;size=7'
, '(.*)(color\=)([^;]+);?(.*)'
, '\3') "colorID"
FROM DUAL;
It is possible there may be less clunky regex solutions, but this one definitely works. Here's a SQL Fiddle.
'.*color\=([^;]+).*', '\1'Try something like this:
SELECT REGEXP_SUBSTR(REGEXP_SUBSTR('product=1627;color=45;size=7', 'color\=([^;]+);?'), '[[:digit:]]+') "colorID"
FROM DUAL;
From Oracle 11g onwards we can specify capture groups in REGEXP_SUBSTR.
SELECT REGEXP_SUBSTR('product=1627;color=45;size=7', 'color=(\d+);', 1, 1, 'i', 1) "colorID"
FROM DUAL;
