英文:
How to find all entries containing a word as a whole word only?
问题
I am using Toad for Oracle and I have a Table called CO_EXCEPT_TEXT_LG.
The Table is similar to the below:
Deviation | Action |
---|---|
1 | UNLOCK PHASE |
2 | UNLOCKED PHASE |
3 | UNLOCK |
4 | To UNLOCK PHASE |
I'm trying to pull all entries that have the word 'UNLOCK' as a whole word only. (In this case, this would be entries 1, 3 and 4.)
I have tried the following so far:
WHERE ACTION = 'UNLOCK'
: gives Entry 3WHERE ACTION LIKE 'UNLOCK'
: gives Entry 3
(I think LIKE without wildcards essentially functions like '='. Correct me if I'm wrong!)
-
WHERE ACTION LIKE '%UNLOCK%'
: gives Entries 1,2,3,4 -
WHERE ACTION LIKE 'UNLOCK%'
: gives Entries 1,2,3
英文:
I am using Toad for Oracle and I have a Table called CO_EXCEPT_TEXT_LG.
The Table is similar to the below:
Deviation | Action |
---|---|
1 | UNLOCK PHASE |
2 | UNLOCKED PHASE |
3 | UNLOCK |
4 | To UNLOCK PHASE |
I'm trying to pull all entries that have the word 'UNLOCK' as a whole word only. (In this case, this would be entries 1, 3 and 4.)
I have tried the following so far:
WHERE ACTION = 'UNLOCK'
: gives Entry 3WHERE ACTION LIKE 'UNLOCK'
: gives Entry 3
(I think LIKE without wildcards essentially functions like '='. Correct me if I'm wrong!)
-
WHERE ACTION LIKE '%UNLOCK%'
: gives Entries 1,2,3,4 -
WHERE ACTION LIKE 'UNLOCK%'
: gives Entries 1,2,3
答案1
得分: 4
假设你想要的单词边界是空格,可以使用以下方法来实现:
where ' ' || action || ' ' like '% UNLOCK %'
我们也可以使用正则表达式:
where regexp_like (action, '(^|\s)UNLOCK(\s|$)')
... 这里的 ^|\s
意味着:要么是字符串的开头 (^
),要么是一个空格 (\s
) - 而 $
代表字符串的结尾。
英文:
Assuming that the word boundaries you want are spaces, here is one way to do it with like
:
where ' ' || action || ' ' like '% UNLOCK %'
We could also use a regex:
where regexp_like (action, '(^|\s)UNLOCK(\s|$)')
... where ^|\s
means: either the beginning of the string (^
) or a space (\s
) - while $
represents the end of the string.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论