CODEWITHSUNDEEP
sqloracle-databaseplsql
Jaipal Singh Dungarpur
Jaipal Singh Dungarpur

Reputation: 41

Search comma separated value in oracle 12

I have a Table - Product In Oracle, wherein p_spc_cat_id is stored as comma separated values.

p_id p_name p_desc p_spc_cat_id
1    AA     AAAA   26,119,27,15,18
2    BB     BBBB   0,0,27,56,57,4
3    BB     CCCC   26,0,0,15,3,8
4    CC     DDDD   26,0,27,7,14,10
5    CC     EEEE   26,119,0,48,75

Now I want to search p_name which have p_spc_cat_id in '26,119,7' And this search value are not fixed it will some time '7,27,8'. The search text combination change every time

my query is:

select p_id,p_name from product where p_spc_cat_id in('26,119,7');

when i execute this query that time i can't find any result

Upvotes: 3

Views: 5100

Answers (6)

Gary_W
Gary_W

Reputation: 10360

This solution uses CTE's. "product" builds the main table. "product_split" turns products into rows so each element in p_spc_cat_id is in it's own row. Lastly, product_split is searched for each value in the string '26,119,7' which is turned into rows by the connect by. 

with product(p_id, p_name, p_desc, p_spc_cat_id) as (
  select 1, 'AA', 'AAAA', '26,119,27,15,18' from dual union all
  select 2, 'BB', 'BBBB', '0,0,27,56,57,4'  from dual union all
  select 3, 'BB', 'CCCC', '26,0,0,15,3,8'   from dual union all
  select 4, 'CC', 'DDDD', '26,0,27,7,14,10' from dual union all
  select 5, 'CC', 'EEEE', '26,119,0,48,75'  from dual
),
product_split(p_id, p_name, p_spc_cat_id) as (
  select p_id, p_name, 
         regexp_substr(p_spc_cat_id, '(.*?)(,|$)', 1, level, NULL, 1)
  from product
  connect by level <= regexp_count(p_spc_cat_id, ',')+1   
    and prior p_id = p_id
    and prior sys_guid() is not null 
)
-- select * from product_split;
select distinct p_id, p_name
from product_split
where p_spc_cat_id in(
  select regexp_substr('26,119,7', '(.*?)(,|$)', 1, level, NULL, 1) from dual
  connect by level <= regexp_count('26,119,7', ',') + 1
)
order by p_id;

      P_ID P_
---------- --
         1 AA
         3 BB
         4 CC
         5 CC

SQL>

Upvotes: 0

pOrinG
pOrinG

Reputation: 935

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)

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

SELECT p_id,p_name
FROM product
WHERE p_spc_cat_id
LIKE '%'||'&i_str'||'%'`

where i_str is 26,119,7 or 7,27,8

Upvotes: 0

Pravin Satav
Pravin Satav

Reputation: 702

If the string which you are searching will always have 3 values (i.e. 2 commas present) then you can use below approach.

    where  p_spc_cat_id like regexp_substr('your_search_string, '[^,]+', 1, 1) 
or p_spc_cat_id like regexp_substr('your_search_string', '[^,]+', 1, 2) 
or p_spc_cat_id like regexp_substr('your_search_string', '[^,]+', 1, 3)

If you cant predict how many values will be there in your search string (rather how many commas) in that case you may need to generate dynamic query.

Unfortunately sql fiddle is not working currently so could not test this code.

Upvotes: 0

Richard Flapper
Richard Flapper

Reputation: 21

Use the Oracle function instr() to achieve what you want. In your case that would be:

SELECT p_name 
FROM product 
WHERE instr(p_spc_cat_id, '26,119') <> 0;

Oracle Doc for INSTR

Upvotes: 0

Ori Marko
Ori Marko

Reputation: 58774

You can use like to find it:

select p_id,p_name from product where p_spc_cat_id like '%26,119%'
or p_spc_cat_id like '%119,26%' or p_spc_cat_id like '%119,%,26%' or p_spc_cat_id like '%26,%,119%';

Upvotes: 0

Related Questions