如何找到所有包含一个单词作为整词的条目?

huangapple go评论70阅读模式
英文:

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:

  1. WHERE ACTION = 'UNLOCK': gives Entry 3
  2. WHERE ACTION LIKE 'UNLOCK': gives Entry 3

(I think LIKE without wildcards essentially functions like '='. Correct me if I'm wrong!)

  1. WHERE ACTION LIKE '%UNLOCK%': gives Entries 1,2,3,4

  2. 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:

  1. WHERE ACTION = 'UNLOCK': gives Entry 3
  2. WHERE ACTION LIKE 'UNLOCK': gives Entry 3

(I think LIKE without wildcards essentially functions like '='. Correct me if I'm wrong!)

  1. WHERE ACTION LIKE '%UNLOCK%': gives Entries 1,2,3,4

  2. 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.

huangapple
  • 本文由 发表于 2023年6月26日 20:28:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/76556687.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定