oracle中的Regexp substr
提问于 2022-02-20 23:21:20
Concerta 18 mg/24 hr oral tablet, extended release预期产出
tablet, extended release输入
glipiZIDE 10 mg oral tablet, extended release预期产出
tablet, extended release输入
Adderall XR 10 mg oral capsule, extended release预期产出
capsule, extended release我使用下面的查询和获取表或胶囊作为输出。
select trim(
regexp_substr(
lower('Adderall XR 10 mg oral capsule, extended release' ),
' ((caps|tab|powd|syr|aero|liq|susp)[a-z]+|solution|lotion|spray|([^a-z0-9])?extended release )+'
)
)
from dual;
回答 1
Stack Overflow用户
发布于 2022-02-20 23:34:04
有那么多你发布的信息(阅读:根本没有解释)，你为什么需要正则表达式？简单substr + instr返回所需的输出：
SQL> with test (id, col) as
2 (select 1, 'Concerta 18 mg/24 hr oral tablet, extended release' from dual union all
3 select 2, 'glipiZIDE 10 mg inhalation aerosol' from dual union all
4 select 3, 'Adderall XR 10 mg subcutaneous solution' from dual
5 )
6 select id,
7 substr(col, case when instr(col, 'oral') > 0 then instr(col, 'oral') + 5
8 when instr(col, 'inhalation') > 0 then instr(col, 'inhalation') + 11
9 when instr(col, 'subcutaneous') > 0 then instr(col, 'subcutaneous') + 13
10 end
11 ) result
12 from test;
ID RESULT
---------- --------------------------------------------------
1 tablet, extended release
2 aerosol
3 solution
SQL>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71202511
复制
腾讯云开发者
