在Oracle 12中搜索逗号分隔的值

我的回答很晚，但是我希望我正确理解了这个问题.

I am little late in answering however i hope that i understood the question correctly.

在以下情况下进一步阅读:您有一个表，该表存储着

Read further if: you have a table storing records like

1. 10,20,30,40
2. 50,40,20,70
3. 80,60,30,40

还有一个搜索字符串，例如"10,60"，在这种情况下，它应该返回第1& 3.

And a search string like '10,60', in which cases it should return rows 1 & 3.

请尝试以下操作，它适用于我的小桌子&amp ;;数据.

Please try below, it worked for my small table & data.

create table Temp_Table_Name (some_id number(6), Ab varchar2(100))
insert into Temp_Table_Name values (1,'112,120')
insert into Temp_Table_Name values (2,'7,8,100,26')

首先让我们细分逻辑:

• 该表在其中一列[AB列]中包含逗号分隔的数据.
• 我们有一个逗号分隔的字符串，需要在该字符串列中进行单独搜索. ['26，119,7,18'-X_STRING]
• ID列是表中的主键.

1.)使表中的每条记录多次x次，其中x是搜索字符串[X_STRING]中逗号分隔值的计数.我们可以使用下面的查询来创建笛卡尔联接子查询表.

1.) Lets multiple each record in the table x times where x is the count of comma separated values in the search string [X_STRING]. We can use below query to create the cartesian join sub-query table.

Select Rownum Sequencer,'26,119,7,18' X_STRING 
from dual 
CONNECT BY ROWNUM <= (LENGTH( '26,119,7,18') - LENGTH(REPLACE( '26,119,7,18',',',''))) + 1

小注释:计算逗号分隔值的数量=

Small note: Calculating count of comma separated values =

Length of string - length of string without ',' + 1 [add one for last value]

2.)创建一个函数PARSING_STRING，使得PARSING_STRING(string，position).所以，如果我通过:

2.) Create a function PARSING_STRING such that PARSING_STRING(string,position). So If i pass:

PARSING_STRING('26,119,7,18',3) it should return 7.

CREATE OR REPLACE Function PARSING_STRING
(String_Inside IN Varchar2, Position_No IN Number) 
Return Varchar2 Is
    OurEnd   Number; Beginn Number;
Begin

    If Position_No < 1 Then 
    Return Null; 
    End If;

    OurEnd := Instr(String_Inside, ',', 1, Position_No);

    If OurEnd = 0 Then
        OurEnd := Length(String_Inside) + 1;
    End If;

    If Position_No = 1 Then
        Beginn := 1;
    Else
        Beginn := Instr(String_Inside, ',', 1, Position_No-1) + 1;
    End If;

    Return Substr(String_Inside, Beginn, OurEnd-Beginn);

End;
/

3.)主查询，具有用于增加记录的联接.

3.) Main query, with the join to multiply records.:

select t1.*,PARSING_STRING(X_STRING,Sequencer) 
from Temp_Table_Name t1,
(Select Rownum Sequencer,'26,119,7,18' X_STRING from dual 
CONNECT BY ROWNUM <= (Select (LENGTH( '26,119,7,18') - LENGTH(REPLACE( 
'26,119,7,18',',',''))) + 1 from dual))  t2

请注意，对于每个相乘的记录，我们从逗号分隔的字符串中得到1个特定的位置值.

Please note that with each multiplied record we are getting 1 particular position value from the comma separated string.

4.)最终确定where条件:

4.) Finalizing the where condition:

Where
/* For when the value is in the middle of the strint [,value,] */
AB like '%,'||PARSING_STRING(X_STRING,Sequencer)||',%'
OR
/* For when the value is in the start of the string [value,] 
parsing the first position comma separated value to match*/
PARSING_STRING(AB,1) = PARSING_STRING(X_STRING,Sequencer)
OR
/* For when the value is in the end of the string [,value] 
parsing the last position comma separated value to match*/
PARSING_STRING(AB,(LENGTH(AB) - LENGTH(REPLACE(AB,',',''))) + 1) = 
PARSING_STRING(X_STRING,Sequencer)

5.)在查询中使用distinct获得唯一的ID

5.) Using distinct in the query to get unique ID's

[最终查询:上述所有逻辑的组合:1个查询以查找所有逻辑]

select distinct Some_ID
from Temp_Table_Name t1,
(Select Rownum Sequencer,'26,119,7,18' X_STRING from dual 
CONNECT BY ROWNUM <= (Select (LENGTH( '26,119,7,18') - LENGTH(REPLACE( '26,119,7,18',',',''))) + 1 from dual))  t2
Where
AB like '%,'||PARSING_STRING(X_STRING,Sequencer)||',%'
OR
PARSING_STRING(AB,1) = PARSING_STRING(X_STRING,Sequencer)
OR
PARSING_STRING(AB,(LENGTH(AB) - LENGTH(REPLACE(AB,',',''))) + 1) = PARSING_STRING(X_STRING,Sequencer)