CODEWITHSUNDEEP
sqlregexoracle-database
Andrey
Andrey

Reputation: 311

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

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?

Upvotes: 10

Views: 9892

Answers (3)

Gayan Dasanayake
Gayan Dasanayake

Reputation: 2021

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;

Upvotes: 0

APC
APC

Reputation: 146179

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.

Upvotes: 6

xray1986
xray1986

Reputation: 1168

Try something like this:

SELECT REGEXP_SUBSTR(REGEXP_SUBSTR('product=1627;color=45;size=7', 'color\=([^;]+);?'), '[[:digit:]]+') "colorID"
FROM DUAL;

Upvotes: 3

Related Questions