如何在Oracle中的字符串中查找精确的字符串匹配 [英] How To find Exact string match within a String in Oracle

问题描述

我有一个oracle表，该表具有一个名为system_access的列，该列包含以下数据:

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"

我不需要带有只读(否)"，后跟之前或之后带有空格或只读(请参阅mgr)"，其后或之前没有空格和括号的记录.

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

如前所述，仅记录与字符串"Read Only"或"read only"完全匹配的记录

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

推荐答案

有时，在比较字符串之前将一些内容添加到字符串中很有用，然后每个元素都采用相同的格式:

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

这篇关于如何在Oracle中的字符串中查找精确的字符串匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！