文本列中的模式匹配

发布于 2024-11-28 02:48:10 字数 484 浏览 0 评论 0原文

我有一个文本列，文本列中的数据如下：

Rob goes to school,get punished
Rob goes to school
Rob does not goes to school,get punished

当尝试使用类似 case 语句编写查询时，

CASE 
    WHEN (PATINDEX('%Rob goes to school%',value) > 0) OR
         (PATINDEX('%Rob is ill%',value) > 0 ) AND
         (PATINDEX(%get punished%',value) > 0) THEN
           'DONE'

它应该仅选择第一个语句，但它会选择带有“DONE”的第一个和第二个语句。在这种情况下有什么建议如何进行模式匹配吗？我正在使用 SQL Sever 2005/2008

原文

I have a text column and the data in the text columns are as below:

Rob goes to school,get punished
Rob goes to school
Rob does not goes to school,get punished

When trying to write a query using case statement like

CASE 
    WHEN (PATINDEX('%Rob goes to school%',value) > 0) OR
         (PATINDEX('%Rob is ill%',value) > 0 ) AND
         (PATINDEX(%get punished%',value) > 0) THEN
           'DONE'

It should select only the 1st statement but instead it is picking both the 1st and 2nd statement with 'DONE'. Any suggestion how to do a pattern match in this case?
I am using SQL Sever 2005/2008

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一曲爱恨情仇 2024-12-05 02:48:10

运算符优先级e 和括号不足

您可能有 x OR y AND z 实际上是x OR (y AND z)。您想要(x OR y) AND z吗？

第二条语句给出 true OR (false AND false) 给出 true
您希望 (true OR false) AND false 给出 false

所以 SQL 应该是

CASE WHEN
   (
      PATINDEX('%Rob goes to school%', value) > 0
      OR
      PATINDEX('%Rob is ill%', value) > 0
   )
   AND
   (PATINDEX(%get punished%', value) > 0) THEN 'DONE'
...

Operator precedence and not enough parenthesis probably

You have x OR y AND z which is actually x OR (y AND z). Do you want want (x OR y) AND z?

The 2nd statement give true OR (false AND false) which gives true
You want (true OR false) AND false to give false

So the SQL should be

CASE WHEN
   (
      PATINDEX('%Rob goes to school%', value) > 0
      OR
      PATINDEX('%Rob is ill%', value) > 0
   )
   AND
   (PATINDEX(%get punished%', value) > 0) THEN 'DONE'
...

回复收藏 0 原文

落在眉间の轻吻 2024-12-05 02:48:10

PATINDEX 不会将字符串视为分隔列表（逗号分隔值）——它会搜索整个字符串的匹配项。

罗布去上学，受到惩罚
罗布去学校

PATINDEX('%罗布去学校%',value) > 0 对于它们两者都计算为 true，因为通配符 % 与 0 或的任何字符串匹配更多字符。你的第二个和第三个模式永远不会被评估。

If you want to test which pattern is returning true, try this:
CASE 
    WHEN (PATINDEX('%Rob goes to school%',value) > 0) THEN 'Pattern 1'
    WHEN (PATINDEX('%Rob is ill%',value) > 0 ) THEN 'Pattern 2'
    WHEN (PATINDEX('%get punished%',value) > 0) THEN 'Pattern 3'
    ELSE 'No Match Found' END

如果您希望模式匹配第一个值，而不是第二个值，则查找带有逗号的 (PATINDEX('%Rob go to school,%',value) > 0) 。

否则 - 如果您想将字符串视为逗号分隔值，PATINDEX 不是您最好的工具。其他选项可能包括通过表值函数将字符串转换为表，或者您有什么。

PATINDEX does not treat your strings as delimited lists (comma-separated values) -- it searches for a match against the entire string.

Rob goes to school,get punished
Rob goes to school

PATINDEX('%Rob goes to school%',value) > 0 evaluates to true for both of them because the wildcard % matches any string of 0 or more characters. Your second and third patterns never get evaluated.

If you want to test which pattern is returning true, try this:
CASE 
    WHEN (PATINDEX('%Rob goes to school%',value) > 0) THEN 'Pattern 1'
    WHEN (PATINDEX('%Rob is ill%',value) > 0 ) THEN 'Pattern 2'
    WHEN (PATINDEX('%get punished%',value) > 0) THEN 'Pattern 3'
    ELSE 'No Match Found' END

If you want a pattern to match the first value, but not the second, then look for (PATINDEX('%Rob goes to school,%',value) > 0) with the comma instead.

Otherwise -- if you're wanting to treat the strings like comma-separated values, PATINDEX is not your best tool for that. Other options might include converting your strings to tables via table-value function, or what have you.

回复收藏 0 原文

~没有更多了~