CODEWITHSUNDEEP
sqloracle-database
user6252041
user6252041

Reputation: 73

Oracle SQL - how to find a match in a column with comma separated values?

I have a column with type CLOB and contains comma separated values like '1,2,13,14' and i need to check if the value in another column (string) can match with any of the comma separated values in the CLOB. How do I do that?

eg. find if 3 is in '1,2,13,14'

I tried InStr and regexp_instr, they will both find a match when trying to see if 3 is in '1,2,13,14'

Please help!!

Upvotes: 1

Views: 3173

Answers (1)

Boneist
Boneist

Reputation: 23578

You can do this by adding a comma to the beginning and end of the string you're checking, and then comparing it to ,3, like so:

with sample_data as (select '1,2,13,14' str from dual union all
                     select '11,22,3' str from dual union all
                     select '3,44,5' str from dual union all
                     select '19,3,394,49' str from dual union all
                     select '33,20' str from dual union all
                     select '3' str from dual union all
                     select '33,303' str from dual)
select str,
       instr(','||str||',', ',3,') instr_3,
       case when ','||str||',' like '%,3,%' then 'Y'
            else 'N'
       end is_3_present
from   sample_data;

STR            INSTR_3 IS_3_PRESENT
----------- ---------- ------------
1,2,13,14            0 N           
11,22,3              7 Y           
3,44,5               1 Y           
19,3,394,49          4 Y           
33,20                0 N           
3                    1 Y           
33,303               0 N

I've given you a couple of different ways of checking.

Upvotes: 3

Related Questions