CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
tonyf
tonyf

Reputation: 35539

How To find Exact string match within a String in Oracle

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"

Upvotes: 2

Views: 15859

Answers (6)

Harrison
Harrison

Reputation: 9090

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)

Upvotes: 1

Nick Pierpoint
Nick Pierpoint

Reputation: 17769

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.

Upvotes: 0

Payload
Payload

Reputation: 1726

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.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36798

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,%';

Upvotes: 5

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 0

Nilesh
Nilesh

Reputation: 201

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

Upvotes: 0

Related Questions