Search List Stored in Column and Eliminate Row with SQL

I have a table (MYTABLE) in an Oracle database that has a column which stores a list of items. Something like:

ITEM_ID	ITEMS
5476301	B, L, M, PP, S, TT
5579504	B, L, M, PP, S, TT
5579504	B, L, PP, S, TT
4005953	L
4064254	L, M
4121291	PP
4162254	M
4189256	M, PP
4255971	M, PP

I am trying to build a SQL query that eliminates any rows where ‘M’ is in the list. So, given the above table, I would like to return the following:

ITEM_ID	ITEMS
5579504	B, L, PP, S, TT
4005953	L
4121291	PP

I have never worked with lists stored in a column before. I am inclined to do something like

SELECT * FROM MYTABLE
WHERE 'M' not in ITEMS

but that doesn’t seem to work. It still returns all rows. I’m also thinking about parsing out each item in the list to evaluate against the criteria, however I haven’t found an example that might show how that would work. I feel like this is simple but I haven’t found a way to do this. Any suggests are appreciated.

Answer

Your column is just a string and not recognized by Oracle as a “list” in any sense. So, you need to implement your logic via string manipulation. E.g.,

SELECT item_id, items
FROM   my_table
WHERE  ','||replace(items,' ','')||',' NOT LIKE '%,M,%';

Full example, for interested readers:

with my_table (item_id, items) as (
SELECT 5476301, 'B, L, M, PP, S, TT' FROM DUAL UNION ALL
SELECT 5579504, 'B, L, M, PP, S, TT' FROM DUAL UNION ALL
SELECT 5579504, 'B, L, PP, S, TT' FROM DUAL UNION ALL
SELECT 4005953, 'L' FROM DUAL UNION ALL
SELECT 4064254, 'L, M' FROM DUAL UNION ALL
SELECT 4121291, 'PP' FROM DUAL UNION ALL
SELECT 4162254, 'M' FROM DUAL UNION ALL
SELECT 4189256, 'M, PP' FROM DUAL UNION ALL
SELECT 4255971, 'M, PP' FROM DUAL )
SELECT item_id, items
FROM   my_table
WHERE  ','||replace(items,' ','')||',' NOT LIKE '%,M,%';

+---------+-----------------+
| ITEM_ID |      ITEMS      |
+---------+-----------------+
| 5579504 | B, L, PP, S, TT |
| 4005953 | L               |
| 4121291 | PP              |
+---------+-----------------+

Editorial: this is almost certainly not a good data model to implement or keep, if you have any time and authority to change it.