如何在 Oracle 中的字符串中找到精确的字符串匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4102825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How To find Exact string match within a String in Oracle
提问by tonyf
I have an oracle table that has a column called system_access that has the following data:
我有一个 oracle 表，其中有一个名为 system_access 的列，其中包含以下数据：
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.
我不需要具有“只读（否）”的记录，在它之后或之前有空格然后括号，或者在它之后或之前没有空格和括号的“只读（参见 mgr）”。
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"
如前所述，只有与字符串“只读”或“只读”完全匹配的记录
回答by Jon Heller
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,%';
回答by Harrison
to take @Bob Jarvis (although I couldn t get his to work with the data sample below) one step farther and drop the OR
采取@Bob Jarvis（虽然我无法让他使用下面的数据样本）更进一步并放下 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.
REGEXP_LIKE(SYSTEM_ACCESS, '(read only[ ,]+[^/(])|(read only$)','i');这将查找后面可能有空格或逗号的所有“只读”实例它但禁止打开 ( 或字符串末尾的“只读”。
(http://www.regular-expressions.info/oracle.html & http://psoug.org/snippet.htm/Regular_Expressions_Regex_Cheat_Sheet_856.htm?PHPSESSID=7238be874ab99d0731a9da64f2dbafd8)
（http://www.regular-expressions.info/oracle.html & http://psoug.org/snippet.htm/Regular_Expressions_Regex_Cheat_Sheet_856.htm?PHPSESSID=7238be874ab99d0731a9da64f2dbafd8）
回答by Nilesh
select * from table where lower(trim(column_name)) = 'read only'
select * from table where lower(trim(column_name)) = '只读'
回答by Bob Jarvis - Reinstate Monica
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.
分享和享受。
回答by Payload
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.
只要表数据不改变，这就会起作用。使用 REGEX 也可以工作，但必须一次又一次地正确制作正则表达式，如果数据发生变化，一切都是徒劳的。
回答by Nick Pierpoint
The searching "%read only,%"method in @jonearles answerwill 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.
@jonearles 答案中的搜索"%read only,%"方法可以正常工作。但是，由于您一直在笨拙地进行明显疯狂的底层设计，因此对权限测试增加一些严格性可能是合适的。
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 yourdefinition of 'Read-Only' that you could use in your query.
您可能还希望通过针对包含已知角色集的表检查解析的权限字符串中的每个标记来扩展此功能，即“只读”、“只读（参见 mgr）”。该表甚至可以包含与您可以在查询中使用的“只读”定义相匹配的标志。
