SQL match exact word

Question

I know this query returns all rows that contains white :

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

But I want to get rows like "white rose" not like "whiterose". How can I edit that query to get only contains "white" as a single word? Thanks in advance.

Answer 1

Try this by finding with word with space character on both sides.

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

Appending space to title on both sides is to cover for cases where the word comes at the beginning or end of the string.

Answer 2

The other answers don't take punctuation into account.

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

Assuming that words are separated by spaces, you can do:

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

Alternatively, you may want full text search capabilities.

Answer 4

Includes punctuation, as Ghost pointed out:

select title 
from t
where ' '+title+' ' like '%[^a-z]white[^a-z]%'

---

rextester demo: http://rextester.com/DBVH10759

create table t (title varchar(32));
insert into t values
 ('white')
,('whiteboard')
,('offwhite')
,('white house')
,('something white.')

select title 
from t
where ' '+title+' ' like '%[^a-z]white[^a-z]%'

returns

+------------------+
|      title       |
+------------------+
| white            |
| white house      |
| something white. |
+------------------+

Answer 5

What you're looking for is, I presume:

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