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
And a search string like '10,60', in which cases it should return rows 1 & 3.
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')
Firstly lets breakdown the logic:
- The table contains comma separated data in one of the columns[Column AB].
- We have a comma separated string which we need to search individually in that string column. ['26,119,7,18'-X_STRING]
- ID column is primary key in the table.
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.) 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.) 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
Please note that with each multiplied record we are getting 1 particular position value from the comma separated string.
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.) Using distinct in the query to get unique ID's
[Final Query:Combination of all logic stated above: 1 Query to find them all]
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)
