How to search a comma separated DB column in Oracle

Question

I have an Oracle DB table with one column that contains comma-separated values (not my design!). In a web application when user attempts to add an object that results in making a new entry into this table, I am trying to check for duplicates.

I know how to check for single value (e.g. trying to add "ABC") but not sure how to do it if user is adding ABC, DEF, GHI, ...

Let's say table is called PRINTER_MAPPING_CFG and column in question in called RUNOUTS. A typical row might look like:

001, 002, 006, 008, 009

I use the following to check for single value:

SELECT COUNT(*) 
FROM PRINTER_MAPPING_CFG 
WHERE ',' || RUNOUTS || ',' LIKE '%,' || '001' || ',%'

If user is adding 003, 004, 006, 007, 008, I am not sure how to proceed (here 006 and 008 are already in the table).

I can split and search for each separately but looks wasteful, if there is an alternative.

Answer 1

Right, not the best design one could imagine.

See whether this helps:

SQL> with
  2  printer_mapping_cfg (id, runouts) as
  3    -- this is what you currently have. I included the ID column
  4    -- as you probably don't have just one row in that table, do you?
  5    (select 1, '001, 002, 006, 008, 009' from dual union all
  6     select 2, '005, 006, 007'           from dual
  7    ),
  8  new_value (runouts) as
  9    -- this is what user enters
 10    (select '003, 004, 006, 007, 008' from dual),   --> 006 and 008 exist for ID = 1
 11  split_pmc as                                      --> 006 and 007 exist for ID = 2
 12    (select p.id,
 13            trim(regexp_substr(p.runouts, '[^,]+', 1, column_value)) val
 14     from printer_mapping_cfg p cross join
 15         table(cast(multiset(select level
 16                             from dual
 17                             connect by level <= regexp_count(p.runouts, ',') + 1
 18                            ) as sys.odcinumberlist))
 19    )
 20  select s.id,
 21         listagg(s.val, ', ') within group (order by s.val) duplicates
 22  from split_pmc s
 23  where s.val in (select trim(regexp_substr(n.runouts, '[^,]+', 1, level))
 24                  from new_value n
 25                  connect by level <= regexp_count(n.runouts, ',') + 1
 26                 )
 27  group by s.id
 28  order by s.id;

        ID DUPLICATES
---------- ------------------------------
         1 006, 008
         2 006, 007

SQL>

What does it do?

• lines #1 - 7 represent data you already have
• lines #8 - 10 is an input string, the one that user types
• lines #11 - 19 are used to split existing values into rows
• lines #20 - 28 represent the final select, where
  • lines #23 - 26 check whether values we found in split_pmc CTE exist in newly added values (which are already split into rows)
  • line #21 aggregates duplicate values into a single string

[EDIT]

As you already have the PRINTER_MAPPING_CFG table, your code would begin with

SQL> with
  2  new_value (runouts) as ...

You'd still reference the PRINTER_MAPPING_CFG table just as I did.

Another condition(s) would be added somewhere at line #18 (in code I posted above).