How to get part of the string that matched with regular expression in Oracle SQL

oracle, regex, sql

Solution

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.

Problem

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?

Original source