regexp_substr 使用 % 拾取类似于 like condition 的单词

这是一种选择;看看是否有帮助。

一些示例数据：

SQL> with customer (col) as
  2    (select 'I am not an immigrant'                         from dual union all
  3     select 'I want immigration to be found'                from dual union all
  4     select 'Who is "imigrant"?'                            from dual union all
  5     select 'Yep, that is immigration we are talking about' from dual union all
  6     select 'What? IMMIGRATION on TV?'                      from dual union all
  7     select 'Imagine all the people'                        from dual union all
  8     select 'Awful migraine in my head'                     from dual union all
  9     select 'No such word here'                             from dual
 10    )

查询：

 11  select col,
 12    regexp_substr(col, 'im.*g\w+', 1, 1, 'i') result
 13  from customer
 14  where lower(col) like '%im%g%';

COL                                           RESULT
--------------------------------------------- ---------------------------------------------
I am not an immigrant                         immigrant
I want immigration to be found                immigration
Who is "imigrant"?                            imigrant
Yep, that is immigration we are talking about immigration
What? IMMIGRATION on TV?                      IMMIGRATION
Imagine all the people                        Imagine

6 rows selected.

SQL>

select a.*,
       regexp_susbtr (comment, '.mmig+\w*|.mig+\w*') as selected
from   customer a
where  lower (comment) like %imm%g%'
or     lower (comment) like %im%g%;

regexp_susbtr (comment,'.mmig+\w*|.mig+\w*'<<<== 只拾取小写字母，拾取任何小写、写大写或混合写字母的正确编码是什么

请参阅 REGEXP_SUBSTR 文档

语法

...

该参数记录为：match_param

• match_param是数据类型的字符表达式，或者用于更改函数的默认匹配行为。VARCHAR2CHAR

  match_param的值可以包含以下一个或多个字符：

  • 'i'指定不区分大小写的匹配，即使确定的条件排序规则区分大小写也是如此。

所以你想要：

SELECT a.*,
       REGEXP_SUBSTR(comment, '\w*mig\w*', 1, 1, 'i') AS selected
FROM   customer a
WHERE  REGEXP_LIKE(comment, 'im\w*?g', 'i');

哪里：

• \w*mig\w*将匹配零个或多个（尽可能多的）单词字符，然后是子字符串，然后是零个或多个（尽可能多的）单词字符，并且 match 参数使匹配不区分大小写。mig'i'
• 'im\w*?g'匹配子字符串，然后匹配零个或多个单词字符（尽可能少），然后匹配子字符串和 match 参数，使匹配不区分大小写。img'i'

有什么替代方法可以替换查找 AND FOR 和 之间的任何字母%mgregexp_substrregexp_like

如果你想要单词字符 to 和 to 在任何情况下和下划线，那么使用类似 PERL 的模式。az09_\w

如果您只想要字母字符，则使用 POSIX 字符类或 或不区分大小写的匹配。[[:alpha:]][a-zA-Z][a-z]

因此，仅对于字母字符：

SELECT a.*,
       REGEXP_SUBSTR(comment, '[[:alpha:]]*mig[[:alpha:]]*', 1, 1, 'i') AS selected
FROM   customer a
WHERE  REGEXP_LIKE(comment, 'im[[:alpha:]]*?g', 'i');

艺术

SELECT a.*,
       REGEXP_SUBSTR(comment, '[a-z]*mig[a-z]*', 1, 1, 'i') AS selected
FROM   customer a
WHERE  REGEXP_LIKE(comment, 'im[a-z]*?g', 'i');