我们可以在查询中比较LHS = RHS

I have a query where I input username as a single string:

我有一个查询,我输入用户名作为单个字符串:

 'MONTY,JONTY'

My query part looks like:

我的查询部分如下:

SELECT *
  FROM tbl_dummy
 WHERE username IN (SELECT regexp_substr(:username, '[^,]+', 1, level)
                      FROM dual
                   CONNECT BY regexp_substr(:username, '[^,]+', 1, level) IS NOT NULL);

Here my column 'Username' will have data like:

在这里,我的专栏'Username'将包含以下数据:

Monty, Jonty
Monty
Jonty
Jonty, Monty

So when I pass my string, it will split i.e 'Monty', 'Jonty' and the query comparison will miss two values "Monty, Jonty" and "Jonty, Monty" out of 4 rows.

因此,当我传递我的字符串时,它会分裂,即'Monty','Jonty',查询比较将错过4行中的两个值“Monty,Jonty”和“Jonty,Monty”。

If i was able to split my column value while comparison, then i could have proved LHS = RHS.

如果我能够在比较时分割我的列值,那么我可以证明LHS = RHS。

So it would be ('JONTY','MONTY') = ('MONTY','JONTY')

所以它会('JONTY','MONTY')=('MONTY','JONTY')

Is there any way this functionality can be achieved ? I cannot write stored procedure so it has to be an oracle query.

有什么办法可以实现这个功能吗?我无法编写存储过程,所以它必须是一个oracle查询。

Also, Has anyone used RegExp_Like for such thing ? I am not able to find a syntax which would fit this code.

此外,有没有人使用RegExp_Like这样的事情?我无法找到适合此代码的语法。

2 个解决方案

#1

If I understand your question correctly, and if your comma-delimited values aren't too long (regexes in Oracle are limited to 512 bytes IIRC), you can replace the comma-delimited list with a pipe (|)-delimited list and use `REGEXP_LIKE()' as follows:

如果我正确理解了您的问题,并且您的逗号分隔值不是太长(Oracle中的正则表达式限制为512字节IIRC),您可以使用管道(|)定界列表替换逗号分隔列表并使用`REGEXP_LIKE()'如下:

WITH u1 AS (
    SELECT 'MONTY, JONTY' AS username FROM dual
    UNION
    SELECT 'MONTY' AS username FROM dual
    UNION
    SELECT 'JONTY' AS username FROM dual
    UNION
    SELECT 'JONTY, MONTY' AS username FROM dual
), u2 AS (
    SELECT TRIM(REGEXP_SUBSTR('JONTY, MONTY','[^,]+', 1, LEVEL)) AS username FROM dual 
    CONNECT BY REGEXP_SUBSTR('JONTY, MONTY', '[^,]+', 1, LEVEL) IS NOT NULL
)
SELECT * FROM u1
 WHERE EXISTS (
   SELECT 1 FROM u2
    WHERE REGEXP_LIKE(u2.username, '^(' || REGEXP_REPLACE(u1.username, '\s*,\s*', '|') || ')$', 'i')
)

#2

Simply use LIKE operator.

只需使用LIKE运算符即可。

where column_name like '℅MONTY℅' or column_name like '℅JONTY℅'

其中column_name喜欢'℅MONTY℅'或column_name喜欢'℅JONTY℅'

You are trying to use varying-IN list in the predicate. In your case, 'MONTY, JONTY' is a single string.

您正尝试在谓词中使用vary-IN列表。在你的情况下,'MONTY,JONTY'是一个字符串。

#1

WITH u1 AS (
    SELECT 'MONTY, JONTY' AS username FROM dual
    UNION
    SELECT 'MONTY' AS username FROM dual
    UNION
    SELECT 'JONTY' AS username FROM dual
    UNION
    SELECT 'JONTY, MONTY' AS username FROM dual
), u2 AS (
    SELECT TRIM(REGEXP_SUBSTR('JONTY, MONTY','[^,]+', 1, LEVEL)) AS username FROM dual 
    CONNECT BY REGEXP_SUBSTR('JONTY, MONTY', '[^,]+', 1, LEVEL) IS NOT NULL
)
SELECT * FROM u1
 WHERE EXISTS (
   SELECT 1 FROM u2
    WHERE REGEXP_LIKE(u2.username, '^(' || REGEXP_REPLACE(u1.username, '\s*,\s*', '|') || ')$', 'i')
)

#2