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

下面的解决方案使用纯SQL(无过程/函数);它适用于任何级别的嵌套括号和“同级”括号;并且每当输入为空时它返回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(在代码的底部),我们在flag = 1的情况下然后…来定义目标列,添加如下条件：

... , 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