sql - Oracle SQL 获取括号中的最后一个字符串(也可能包含括号)

Question

我正在使用这个查询:

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, '\(.*?\)', 1, REGEXP_COUNT(strain.name, '\(.*?\)')) AS name
FROM (
  SELECT strain.id, TRIM(SUBSTR(strain.name, 1, INSTR(strain.name, '[')-1)) AS name
  FROM species_strain strain
) strain
WHERE INSTR(strain.name, '(', 1, 1) > 0

查询以某种方式工作，但如果我在主括号中得到另一组括号，它就会中断并且我会丢失一些数据。它返回如下内容:

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

附加要求

我确实忘记提到我需要的一组括号应该放在最后。如果它后面还有其他字符，那么我不想要它。我在示例中添加了另一行。

Answer 1

下面的解决方案使用纯 SQL(无过程/函数)；它适用于任何级别的嵌套括号和“同级”括号；每当输入为 null，或不包含任何右括号，或包含右括号但最右边的右括号不平衡时，它都会返回 null (没有左括号，在这个最右边的右括号的左边，所以这对是平衡的)。

在最底部，我将展示仅当最右边的括号是输入字符串中的最后一个字符时才返回“结果”所需的微小调整，否则返回 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, name, instr(name, ')', -1)
       from   species_str
     ),
     rec ( id, name, str, len, prev_pos, new_pos, flag ) as (
       select  id, name, substr(name, 1, instr(name, ')', -1)),
               pos, pos - 1, pos, null
         from  prep
       union all
       select  id, name, str, len, new_pos,
               instr(str, '(',  -(len - new_pos + 2)),
               case when length(replace(substr(str, new_pos), '(', '')) =
                         length(replace(substr(str, new_pos), ')', ''))
                    then 1 end
         from  rec
         where prev_pos > 0 and flag is null
     )
select   id, name, case when flag = 1 
              then substr(name, prev_pos, 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(在代码的底部)，我们有 case when flag = 1 then... 来定义 target 列，添加如下条件:

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

输出修改后:

        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                                          
       151                                                                  
       152 try (this (and (this))) ok?                                      
       153 try (this (and (this)) ok?)      (this (and (this)) ok?)         
       154 try (this (and) this (ok))?                                      
       155 try (this (and (this)            (this)                          
       156 right grouping (includging ")")                                  
       157 try this out ) ( too                                             

 14 rows selected