How To find Exact string match within a String in Oracle?

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,%.

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).

I think you're right, the two regexp's can be combined with a "|" between them. As I said, I'm far from a regular expression guru... :-) – Bob Jarvis Nov 5 '10 at 22:22 @BobJarvis, nor am I! I try to find reasons to use it each day just to get more fluent with it, it's one of those things that I am really starting to love in find and replace – Harrison Nov 8 '10 at 13:01.

Select * from table where lower(trim(column_name)) = 'read only.

I think you have missed that there is not only one piece of data in this column but multiple "Read Only, Write, read only, Admin" – tonsils Nov 5 '10 at 1:55.

Try SELECT * FROM TABLE WHERE REGEXP_LIKE(system_access, '^(). Read only. ^(),', 'i') OR REGEXP_LIKE(system_access, '^().

Read only. ^()$', 'i'); I think this will work for the data shown but it's late, I'm far from a regular expression guru, and I don't have docs handy. Share and enjoy.

Select * from table where lower(system_access) like '%read only,%'; This will work as long as the table data doesn't change. Using REGEX will work as well but the regex would have to be crafted correctly and again, if the data changes it all is for naught.

1 This won't work if the "read only" happens to be at the end of the list. – Craig Nov 5 '10 at 3:25 ...although it will if lower(system_access) is changed to lower(system_access) || ','. – Mark Bannister Nov 5 '10 at 13:31 Yes, but the full table content was included in the question, this is why I clarified my answer with 'as long as the table data doesn't change' – Payload Nov 8 '10 at 0:18.

The searching "%read only,%" method in @jonearles answer will work fine. However, because of the clearly mad underlying design you've been lumbered with, it might be appropriate to add a bit of rigour to the permissions test. I'd be tempted to assume you always have a comma-delimited string and when doing a permissions test parse the text and check for the specific entries "Read Only" and "read only".

You might also look to extend this by checking each token in the parsed permission string against a table containing the known set of roles, i.e. "Read Only", "read only(see mgr)". This table could even contain a flag that matched your definition of 'Read-Only' that you could use in your query.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.