英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论