SQL匹配精确字

Question

我知道此查询返回包含白色的所有行：

SELECT COUNT(*) FROM MyTable WHERE Title like '%white%'

但是我想要像“白玫瑰”这样的行，而不是“白玫瑰”。如何编辑该查询，使其只包含“白色”作为一个单词？提前谢谢。

Answer 1

试着用两边都有空格字符的单词来查找。

SELECT COUNT(*) FROM MyTable WHERE ' ' + Title + ' ' like '% white %'

在两边的标题上附加空格是用来覆盖单词出现在字符串的开头或结尾的情况。

Answer 2

其他答案不考虑标点符号。

DECLARE @Table TABLE (
    Title NVARCHAR(4000)
)

INSERT INTO @Table (Title) SELECT 'White Christmas was a song.'
INSERT INTO @Table (Title) SELECT 'I''m dreaming of a white Christmas.'
INSERT INTO @Table (Title) SELECT 'The snow was white.'
INSERT INTO @Table (Title) SELECT 'Whitewolves are dangerous.'
INSERT INTO @Table (Title) SELECT 'Snowwhite was a female.'


SELECT *
FROM (
    SELECT Title FROM @Table
    WHERE Title LIKE '%white%'
) match
WHERE Title NOT LIKE '%white[a-z]%'
AND Title NOT LIKE '%[a-z]white%'

Answer 3

假设单词由空格分隔，您可以：

WHERE ' ' + Title + ' ' like '% white %'

或者，您可能需要全文搜索功能。