如何在Oracle中将列中的逗号分隔值与字符串集合进行比较

Question

我在数据库中有一个名为“name”的列，该列的值为“john,smith”。我正在向存储过程传递一个字符串变量“name_repository”，其值为“test，test1，john，test2”或“temp，smith，temp1，temp2”。字符串变量“name_repository”的值是在运行时生成的，它们可能是temp或test。

现在这里是我的尝试，我正在尝试选择名称在name_repository中的行。问题是我的名称是“john,smith”，而name_repository只有其中之一。我需要拆分名称变量john和smith，然后与集合进行比较并返回行。

+----------+----------+
| ID     | Name     |
+----------+----------+
| 1      | john,smith  |
| 2      | james,stone    |
| 3      | john,smith |

现在我传递的变量可能是smith、john、james、stone或其他垃圾值。

如果我的参数是smith或john，则应返回第1行和第3行。

查询应该是这样的：

Select * from table where name in name_repository

- gizgok

Answer 1

您可以像这样拆分名称：

SELECT REGEXP_SUBSTR ( name, '[^,]+', 1, LEVEL) data 
FROM table 
CONNECT BY LEVEL <= LENGTH(name) - LENGTH(REPLACE(txt, ',')) + 1

- Kevin Burton

Answer 2

使用Instr()函数查找参数中的逗号
使用Substr()函数从参数的左侧和右侧选择文本。例如：

Substr('john,smith',1,instr('john,smith',',')-1) to give you 'john'
Substr('john,smith',instr('john,smith',',') to give you 'smith'

然后将这些返回值放入你的WHERE子句中

Where 'john' in(name_repository) OR 'smith in(name_repository)

或者......你可以编写自己的分割函数... :-)

- Chains

Answer 3

请查看以下链接： link

似乎下面的解决方案很简洁：

SQL> SELECT str
  2  ,      REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) AS single_element
  3  ,      LEVEL                                 AS element_no
  4  FROM  (
  5         SELECT ROWNUM AS id
  6         ,      str
  7         FROM   t
  8        )
  9  CONNECT BY INSTR(str, ',', 1, LEVEL-1) > 0
10         AND id = PRIOR id
11         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

STR                            SINGLE_ELEMENT                 ELEMENT_NO
------------------------------ ------------------------------ ----------
X,Y,Z                          X                                       1
X,Y,Z                          Y                                       2
X,Y,Z                          Z                                       3
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX                                     1
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y                                       2
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ                                      3
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA                                   4
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B                                       5
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC                                     6
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D                                       7
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E                                       8
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F                                       9
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG                                    10

13 rows selected.
Note that the in-line view is required to alias ROWNUM (as it cannot be used directly in the PRIOR clause on line 10).

- Revious

Answer 4

一个简单的例子可能是：

select * from table
where regexp_replace(name,'^.*,','') in name_repository
or regexp_replace(name,',.*'') in name_repository;

第一个正则表达式从字符串中提取第一个名字，第二个正则表达式从中提取第二个名字。

- Greg Reynolds