Oracle SQL在括号中获取最后一个字符串(也可能包括括号内)

我正在行使此查询：

SELECT strain.id,TRIM(SUBSTR(strain.name,1,INSTR(strain.name,'[')-1)) AS name
FROM species_strain strain

上面的查询给出了相同以下内容：

id    name
-----------------------------------------------
100   CfwHE3 (HH3d) Jt1 (CD-1)
101   4GSdg-3t 22sfG/J (mdx (fq) KO)
102   Yf7mMjfel 7(tm1) (SCID)
103   B29fj;jfos x11 (tmos (line x11))
104   B29;CD (Atm (line G5))
105   Ifkso30 jel-3
106   13GupSip (te3x) Blhas/J           --------> I don't want to get (te3x)

我必要一个正则表达式,它会给我最后一组括号的内容(也许可能也许不包罗一组或多组括号内) – 这必要在字符串的末端.假如它在字符串的中间,那么我不想要它.

我想获得的是以下内容：

(CD-1)
(mdx (fq) KO)
(SCID)
(tmos (line x11))
(Atm (line G5))

因此,假如我复制并粘贴我的整个查询,我有这个,但这并没有思量到内里的括号：

SELECT DISTINCT REGEXP_SUBSTR(strain.name,'(.*?)',REGEXP_COUNT(strain.name,'(.*?)')) AS name
FROM (
  SELECT strain.id,'[')-1)) AS name
  FROM species_strain strain
) strain
WHERE INSTR(strain.name,'(',1) > 0

查询以某种方法事变,但假如我在首要的一个内部获得另一组括号,它会间断,我会丢失一些数据.它返回如下内容：

(CD-1)
(mdx (fq)          ---------> missing KO)
(SCID)
(tmos (line x11)   ---------> missing )
(Atm (line G5)     ---------> missing )

附加要求

我忘了提到我必要的括号集应该在最后.假如之后尚有其他字符,那么我不想要它.我在我的例子中添加了另一行.

办理要领

在最底部,我将表现返回“功效”所需的微调,只有当最右边的括号是输入字符串中的最后一个字符时,不然返回null.这是OP的编辑要求.

我建设了几个输入字符串用于测试.请留意,出格是id = 156,智能理会器不会“计较”字符串笔墨中的括号,可能以某种其他方法不是“正常”括号.我的办理方案并没有那么远 – 它将全部括号视为沟通.

计策是从最右边的右括号(假若有至少一个)的位置开始,并从哪里向左移动,一步一步,只通过左括号(假若有的话)并测试是否括号是均衡的.通过较量“测试字符串”之后的长度(除了之后)与长度之间的较量(删除后),可以轻松完成.

特殊：我可以或许行使“尺度”(非正则表达式)字符串函数编写没有正则表达式的办理方案.这应该有助于保持快速.

查询：

with
     species_str ( id,name) as (
       select 100,'CfwHE3 (HH3d) Jt1 (CD-1)'         from dual union all
       select 101,'4GSdg-3t 22sfG/J (mdx (fq) KO)'   from dual union all
       select 102,'Yf7mMjfel 7(tm1) (SCID)'          from dual union all
       select 103,'B29fj;jfos x11 (tmos (line x11))' from dual union all
       select 104,'B29;CD (Atm (line G5))'           from dual union all
       select 105,'Ifkso30 jel-3'                    from dual union all
       select 106,'13GupSip (te3x) Blhas/J'          from dual union all
       select 151,''                                 from dual union all
       select 152,'try (this (and (this))) ok?'      from dual union all
       select 153,'try (this (and (this)) ok?)'      from dual union all
       select 154,'try (this (and) this (ok))?'      from dual union all
       select 155,'try (this (and (this)'            from dual union all
       select 156,'right grouping (includging ")")'  from dual union all
       select 157,'try this out ) ( too'             from dual
     ),prep ( id,name,pos ) as (
       select id,instr(name,')',-1)
       from   species_str
     ),rec ( id,str,len,prev_pos,new_pos,flag ) as (
       select  id,substr(name,-1)),pos,pos - 1,null
         from  prep
       union all
       select  id,instr(str,-(len - new_pos + 2)),case when length(replace(substr(str,new_pos),'')) =
                         length(replace(substr(str,''))
                    then 1 end
         from  rec
         where prev_pos > 0 and flag is null
     )
select   id,case when flag = 1 
              then substr(name,len - prev_pos + 1) end as target
from     rec
where    flag = 1 or prev_pos <= 0 or name is null
order by id;

输出：

ID NAME                             TARGET                         
---------- -------------------------------- --------------------------------
       100 CfwHE3 (HH3d) Jt1 (CD-1)         (CD-1)                          
       101 4GSdg-3t 22sfG/J (mdx (fq) KO)   (mdx (fq) KO)                   
       102 Yf7mMjfel 7(tm1) (SCID)          (SCID)                          
       103 B29fj;jfos x11 (tmos (line x11)) (tmos (line x11))               
       104 B29;CD (Atm (line G5))           (Atm (line G5))                 
       105 Ifkso30 jel-3                                                    
       106 13GupSip (te3x) Blhas/J          (te3x)                          
       151                                                                  
       152 try (this (and (this))) ok?      (this (and (this)))             
       153 try (this (and (this)) ok?)      (this (and (this)) ok?)         
       154 try (this (and) this (ok))?      (this (and) this (ok))          
       155 try (this (and (this)            (this)                          
       156 right grouping (includging ")")                                  
       157 try this out ) ( too                                             

 14 rows selected

必要举办变动以满意OP(编辑)的要求：

在最表面的select(在代码的底部),我们在flag = 1的环境下然后…来界说方针列,添加如下前提：

...,case when flag = 1 and len = length(name) then ...

通过此修改输出：

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!