如何获得与Oracle SQL中的正则表达式匹配的字符串的一部分 [英] How to get part of the string that matched with regular expression in Oracle SQL

问题描述

可以说我在表的某些字段中具有以下字符串:'product = 1627; color = 45; size = 7'. 我想查询颜色并得到45.

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.

使用此查询:

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

我得到了:

colorID  
---------
color=45;
1 row selected

.

是否有可能获得匹配字符串的一部分-本例中为45?

Is it possible to get part of the matched string - 45 for this example?

推荐答案

一种方法是使用REGEXP_REPLACE.您需要将整个字符串定义为正则表达式模式，然后仅将所需的元素用作replace string.在此示例中，ColorID是整个字符串中的第三个图案

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;  

可能会出现笨拙的正则表达式解决方案，但这绝对可以. 这是一个SQL提琴.

It is possible there may be less clunky regex solutions, but this one definitely works. Here's a SQL Fiddle.

这篇关于如何获得与Oracle SQL中的正则表达式匹配的字符串的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！