Exact match when using LIKE

I am using MS SQL 2000 and would like to get the exact text values when doing a search. However when i use a LIKE it brings back more than i need.

For example some rows contain this and i want just 'the'

then i did this
the best one
there we go
testthe water
the there then
then there the

This is the query i have come up with, is this the only way of doing it where i look for the combinations for the start, middle and end taking into account spaces. Seems very long winded.

select * from test
where test like 'the %' or test like '% the' or test like '% the %'

Who is Participating?
SvenConnect With a Mentor Tech Lead Web-DevelopmentCommented:
try this:
select * from test where CHARINDEX(test, ' the ') > 0

CHARINDEX(string to search in, string to search for, starting position in string)
returns position of string.
sql-server does not have brilliant text string matching services.
The approach you have is the best sql-server offers at the moment.

select * from test
where test like 'the %' or test like '% the ' or test like '% the %'

the additional space in the second set '% the' to '% the ' should make all the difference.
Using the sql-given (modified)

This will eliminate/include the following:
then i did this -- eliminated from list
the best one -- included in list
there we go  -- eliminated from list
testthe water -- eliminated from list
the there then -- included in list
then there the -- included in list

LowfatspreadConnect With a Mentor Commented:
go with charindex perhaps...

do you also need to consider

'.The '
or ',the '

if punctuation is being used

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.