Oracle使用REGEXP_SUBSTR返回括号内的值 [英] Oracle using REGEXP_SUBSTR to return values within brackets

问题描述

我们在数据库中存储了一些应用程序参数，我正在寻找一种通过SQL提取/解析参数的方法.此类数据的示例如下:

We store some application parameters in our database and I am looking for a method via SQL to extract/parse the parameters out. An example of this data would be the following:

PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]

因此，例如Projection的值将为"Transverse"，而引号将是其中的一部分.我认为最简单的方法是使用REGEXP_SUBSTR函数.这是我目前的尝试:

So for example the value for Projection would be "Transverse" and quotes would be part of that. I figured the easiest approach to getting this out would be using the REGEXP_SUBSTR function. This is my current attempt:

SELECT REGEXP_SUBSTR('PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]', 'PROJECTION\[[^][]*\]') FROM dual;

尽管返回:

PROJECTION["Transverse"]

我意识到我可以去除不需要的字符，但是我很好奇是否有人可以推荐任何REGEX调整项，以便SELECT查询在此示例中仅返回"Transverse".投影并不总是我想要的，例如，如果我想要Unit，那么我需要返回:"Two"，2

I realize that I could then just strip out the unneeded characters but I was curious if someone could recommend any REGEX tweaks so that the SELECT query instead returns simply "Transverse" for this example. Projection is not always what I'd want to pull out though, for example if I wanted Unit then I'd need returned: "Two",2

这正在使用Oracle数据库10g企业版10.2.0.5.0-64bi

This is using Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

推荐答案

SELECT REGEXP_REPLACE(
   'PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]',
   '^.*PROJECTION\[(.+?)\].*$', '\1'
) FROM dual

这篇关于Oracle使用REGEXP_SUBSTR返回括号内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！