限制regexp_substr表达式的第2部分
因此，在前几天的帮助下-我取得了长足进步，但现在遇到了一个新问题-以下代码-我正在尝试做两件事-
1）使用单词car-（如果它是其他单词）（即卡）时，cardiac-不要返回错误的值。
2）如果两个词之间存在标点符号，例如逗号或句号，则也不会返回值。
我在很多地方都尝试过（^。），但没有取得很大的成功。
任何建议欢迎-
with test (id, col) as (select 1, 'Delivery in car. Brought' from dual union all select 2, 'we had delivered a nice card' from dual union all select 3, 'Born in the car blah blah blah' from dual union all select 4, 'having brought in by ambulance. She had PN care' from dual ) select id, regexp_substr(col, '(born|birth|home|deliv\w+|ambulance|car).{0,20}(deliv\w+|birth|home|ambulance|car)',1,1,'i') result
from test
results in Delivery in car -- correct delivered a nice card --not correct. If the word is anything but car do not want result Born in the car - -correct ambulance. She had PN car --not correct. If there is punctuation that breaks up sentence then returns null value
问题来源于stack overflow
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
-
使用(^|[^a-z])相匹配的字符串的开始或你匹配和单词之前非字母字符([^a-z]|$)匹配一个非字母字符或单词，这样你就不会像匹配的话后面的字符串的结尾vicar或unborn：
with test (id, col) as ( select 1, 'Delivery in car. Brought' from dual union all select 2, 'The car brought in the delivery.' from dual union all select 3, 'we had delivered a nice card' from dual union all select 4, 'Born in the car blah blah blah' from dual union all select 5, '"In the car, the born the baby was" said Yoda' from dual union all select 6, 'having brought in by ambulance. She had PN care' from dual union all select 7, 'The car brought the mother and unborn baby' from dual union all select 8, 'The vicar said prayers for the baby.' from dual ) select id, col from test where REGEXP_LIKE( col, '(^|[^a-z])(born|birth|home|deliver(y|ed)||ambulance)([^a-z]|$)', 'i' ) and REGEXP_LIKE( col, '(^|[^a-z])car([^a-z]|$)', 'i' ) 输出：
ID | COL
-：| ：-------------------------------------------- 1 | 交付汽车。带来了
2 | 这辆车带来了送货。
4 | 出生在车上等等等等
5 | 尤达说：“在车上，婴儿就是婴儿。”2019-11-18 11:25:45赞同 展开评论