SQL SELECT LIKE仅包含特定单词 [英] SQL SELECT LIKE containing only specific words

问题描述

我有这个查询:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
AND column1 LIKE '%word2%'
AND column1 LIKE '%word3%'

我需要修改此查询以返回其列1包含word1 word2和word3以及其他所有内容的记录！没有其他词，只有这些词.

I need to modify this query to return records for which column1 contains word1 word2 and word3 and nothing else! no other words, just these words.

示例:搜索samsung galaxy s3应该返回samsung s3 galaxy的任意组合，但不能返回samsung galaxy s3 lte

Example: searching for samsung galaxy s3 should return any combination of samsung s3 galaxy but NOT samsung galaxy s3 lte

推荐答案

假定 column1包含以空格分隔的单词，而您只想匹配整个个单词，类似:

Assuming that column1 contains space separated words, and you only want to match on whole words, something like:

SELECT * FROM
  (select ' ' + REPLACE(column1,' ','  ') + ' ' as column1 from mytable) t
WHERE
   column1 like '% word1 %' AND
   column1 like '% word2 %' AND
   column1 like '% word3 %' AND
   REPLACE(REPLACE(REPLACE(column1,
      ' word1 ',''),
      ' word2 ',''),
      ' word3 ','') = ''

请注意，此构造确实允许相同单词出现多次.从这个问题尚不清楚是否应该允许这样做. (小提琴)

Note that this construction does allow the same word to appear multiple times. It's not clear from the question whether that should be allowed. (Fiddle)

如果将这些单词作为单独的行存储在与mytable相关的单独表中，那将是一个更好的设计.然后，我们可以使用更普通的SQL来满足此查询.您的示例看起来像是某种标记示例.拥有一个将每个标签存储为单独行的表(如果需要，还记录了序数位置)会将其变成一个简单的关系划分问题.

It would be a far better design if these words were stored as separate rows in a separate table that relates back to mytable. We could then use more normal SQL to satisfy this query. Your example looks like it's some kind of tagging example. Having a table storing each tag as a separate row (with an ordinal position also recorded, if required) would turn this into a simple relational division problem.

一种计算单词在列中出现多少次的方法是表达式:

A way to count how many times a word appears in a column is the expression:

(LEN(column2) - LEN(REPLACE(column2,'word',''))/LEN('word')

但是这将再次回到较大单词的匹配子序列以及单词本身，而无需进行更多工作.

but this would again revert back to matching subsequences of larger words as well as the word itself, without more work.

这篇关于SQL SELECT LIKE仅包含特定单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！