Query an Oracle CLOB column based on an IN clause

Question

I have a table that has the following definition:

Table name: MY_TAB

ID          NUMBER,
ACCESS_LVL  CLOB

Some example data that may exist within this table is as follows:

ID: 1
ACCESS_LVL: RoleName-A,RoleName-B,RoleName-C,RoleName-D,RoleName-E

ID: 2
ACCESS_LVL: RoleName-D,RoleName-E

ID: 3
ACCESS_LVL: RoleName-A,RoleName-B,RoleName-C,RoleName-D,RoleName-E,RoleName-F,RoleName-G,RoleName-H

ID: 4
ACCESS_LVL: RoleName-E

The query I am unsure how to do is that I need to return all the IDs that may have the following ACCESS_LVL values (where ACCESS_LVL is a clob), i.e.

RoleName-B,RoleName-C,RoleName-D

Basically something like:

select id
from my_tab
where ('RoleName-B','RoleName-C','RoleName-D') in (ACCESS_LVL)

So the result in this example would be just:

ID
1
2
3

Answer 1

Storing comma-separated values in a single column violates every rule of normalization. That's going to make your queries much harder to write and much slower to run. You really ought to have a 1-to-many child table that stores the roles. If you do that, your queries will be much more efficient.

You could do something like

select id
  from my_tab
 where ',' || access_lvl || ',' like '%,RoleName-B,%'
    or ',' || access_lvl || ',' like '%,RoleName-C,%'
    or ',' || access_lvl || ',' like '%,RoleName-D,%'

That is going to be terribly slow but it will work.