How To find Exact string match within a String in Oracle

Question

I have an oracle table that has a column called system_access that has the following data:

Read Only, Write, read only, Admin
Read, Write, read only, Admin
Admin, Read Only (no), read only(see mgr), Admin

Based on the above sample data, I am unsure of my query to only retrieve records that match the exact words of "Read Only" and/or "read only"

I do not need the records that have the "Read Only (no)" with space then bracket after it or before it or "read only(see mgr)" with no space and bracket after it or before it.

So based on above sample data, I would only get back two rows only, i.e.:

Read Only, Write, read only, Admin
Read, Write, read only, Admin

As mentioned, only records that match exactly the string "Read Only" or "read only"

Solution 1

Sometimes it's useful to add something to the string before you compare it, then every element follows the same format:

with testData as
(
  select 'Read Only, Write, read only, Admin' test from dual union all
  select 'Read, Write, read only, Admin'  test from dual union all
  select 'Admin, Read Only (no), read only(see mgr), Admin' test from dual
)
select * from testData
where lower(test)||',' like '%read only,%';

Solution 2

to take @Bob Jarvis (although I couldn t get his to work with the data sample below) one step farther and drop the OR

WITH DATA AS(
SELECT '1- Read Only, Write, read only, Admin' SYSTEM_ACCESS FROM DUAL UNION
SELECT '2- Write, Admin,Read Only' SYSTEM_ACCESS FROM DUAL UNION
SELECT '3- Read, Write, read only, Admin' SYSTEM_ACCESS FROM DUAL UNION
select '4- ADMIN, READ ONLY (NO), READ ONLY(SEE MGR), ADMIN' system_access from dual 
)
select * 
  FROM DATA
 WHERE REGEXP_LIKE(SYSTEM_ACCESS, '(read only[ ,]+[^/(])|(read only$)','i');

REGEXP_LIKE(SYSTEM_ACCESS, '(read only[ ,]+[^/(])|(read only$)','i'); this will look for all instances of "read only" that may have a space or comma after it but prohibits the opening (, or the 'read only' at the end of the string.

(http://www.regular-expressions.info/oracle.html & http://psoug.org/snippet.htm/Regular_Expressions_Regex_Cheat_Sheet_856.htm?PHPSESSID=7238be874ab99d0731a9da64f2dbafd8)