在Oracle中使用regexp_like匹配多个字符串条件

Question

我有一个列叫做关键词在我的Oracle数据库中，基本上列包含其他列的所有数据，以_.

分隔示例表:

NAME  PHONE_NUMBER  COMPANY  ADDRESS  ZIPCODE    KEYWORD
ABCD  9849523459    MICRO    RAJAHMU  532819     ABCD_9849523459_MICRO_RAJAHMU_532819
ABCD  8628738646    INFOS    KAKINAD  532775     ABCD_8628738646_INFOS_KAKINAD_532775
ABCD  8473874381    ICUBE    RAVULAP  537238     ABCD_8473874381_ICUBE_RAVULAP_537238

现在，我怎么能得到精确的字符串匹配使用REGEXP_LIKE。当我使用下面的查询

SELECT * FROM USER_DATA WHERE 1=1 AND REGEXP_LIKE ('KEYWORD', 'ABCD_MICRO_RAVULAP', 'i'));

返回0条记录

我的期望输出应该是

NAME  PHONE_NUMBER  COMPANY  ADDRESS  ZIPCODE    KEYWORD
ABCD  9849523459    MICRO    RAJAHMU  532819     ABCD_9849523459_MICRO_RAJAHMU_532819
ABCD  8473874381    ICUBE    RAVULAP  537238     ABCD_8473874381_ICUBE_RAVULAP_537238

如果有人能帮助我，我将非常感激。

由于Inadvance

Answer 1

像这样?

SQL> with user_data (name, keyword) as
2    (select 'ABCD', 'ABCD_9849523459_MICRO_RAJAHMU_532819' from dual union all
3     select 'DEFG', 'ABCD_8628738646_INFOS_KAKINAD_532775' from dual union all
4     select 'HIJK', 'ABCD_8473874381_ICUBE_RAVULAP_537238' from dual
5    )
6  select *
7  from user_data
8  where regexp_like(keyword, 'ABCD.+MICRO', 'i');
NAME KEYWORD
---- ------------------------------------
ABCD ABCD_9849523459_MICRO_RAJAHMU_532819
SQL>

---

如果你想搜索字符串两个词在任何顺序,使用|操作符:

SQL> with user_data (name, keyword) as
2    (select 'ABCD', 'ABCD_9849523459_MICRO_RAJAHMU_532819' from dual union all
3     select 'DEFG', 'ABCD_8628738646_INFOS_KAKINAD_532775' from dual union all
4     select 'HIJK', 'ABCD_8473874381_ICUBE_RAVULAP_537238' from dual union all
5     select 'LMNO', 'MICRO_241241242_ABCD_WHATEVER_241424' from dual
6    )
7  select *
8  from user_data
9  where regexp_like(keyword, '(ABCD).+(MICRO)|(MICRO).+(ABCD)', 'i');
NAME KEYWORD
---- ------------------------------------
ABCD ABCD_9849523459_MICRO_RAJAHMU_532819
LMNO MICRO_241241242_ABCD_WHATEVER_241424
SQL>

---

然而，这是不实际的。也许你应该考虑Oracle Text特性,然后。

SQL> create table
2  user_data (name, keyword) as
3    (select 'ABCD', 'ABCD_9849523459_MICRO_RAJAHMU_532819' from dual union all
4     select 'DEFG', 'ABCD_8628738646_INFOS_KAKINAD_532775' from dual union all
5     select 'HIJK', 'ABCD_8473874381_ICUBE_RAVULAP_537238' from dual union all
6     select 'LMNO', 'MICRO_241241242_ABCD_WHATEVER_241424' from dual
7    );
Table created.
SQL> create index i1_ud on user_data(keyword) indextype is ctxsys.context;
Index created.
SQL> select *
2  from user_data
3  where contains (keyword, '$micro and abcd', 1) > 0;
NAME KEYWORD
---- ------------------------------------
ABCD ABCD_9849523459_MICRO_RAJAHMU_532819
LMNO MICRO_241241242_ABCD_WHATEVER_241424
SQL>

Answer 2

在底层列上使用IN，而不是尝试解析复合列:

SELECT *
FROM   USER_DATA
WHERE  (name, company, address) IN (
('ABCD', 'MICRO', 'RAVULAP'),
('MICRO', 'ABCD', 'RAVULAP')
);

如果你想比较大小写不敏感，那么使用UPPER:

SELECT *
FROM   USER_DATA
WHERE  (UPPER(name), UPPER(company), UPPER(address)) IN (
('ABCD', 'MICRO', 'RAVULAP'),
('MICRO', 'ABCD', 'RAVULAP')
);

如果您想以任何顺序匹配单个值的三元组，则可以反转IN子句:

SELECT *
FROM   USER_DATA
WHERE  ('ABCD', 'MICRO', 'RAVULAP') IN (
(UPPER(name), UPPER(company), UPPER(address)),
(UPPER(name), UPPER(address), UPPER(company)),
(UPPER(company), UPPER(name), UPPER(address)),
(UPPER(company), UPPER(address), UPPER(name)),
(UPPER(address), UPPER(name), UPPER(company)),
(UPPER(address), UPPER(company), UPPER(name))
);