SQL - selecting a single word?

  • Thread starter Thread starter Tarrant
  • Start date Start date
T

Tarrant

Hello, I am having amazing difficulties in getting SQL to search for a
single word in a text field. I have tried using PATINDEX and LIKE
statements but they don't work, sure they 'work', but not in allowing
me to select a single word, example: "cat" ... I could only do
"%cat%" or "cat%" - pretty much returning any word like category and
similar.

Any suggestions on how-on-earth I can select a single word from a
database without the use of Regex or stored procedures?

Tarrant

Example of attempted and failed statements:

SELECT * FROM blogs WHERE PATINDEX ('[c,C]at' , blogSnippet) > 0
SELECT * FROM blogs WHERE PATINDEX ('%[c,C]at%' , blogSnippet) > 0
SELECT * FROM blogs WHERE PATINDEX ('[c,C]at%' , blogSnippet) > 0
SELECT * FROM blogs WHERE PATINDEX ('cat' , blogSnippet) > 0
SELECT * FROM blogs WHERE PATINDEX ('%cat%' , blogSnippet) > 0
SELECT * FROM blogs WHERE PATINDEX ('cat%' , blogSnippet) > 0
 
What about:

SELECT * FROM blogs WHERE
PATINDEX(' [c,C]at ',blogSnippet) > 0 OR
blogSnippet LIKE '% Cat' OR
blogSnippet LIKE 'Cat %'

That is, include whitespace in what you are looking for ... test for "Cat in
the Hat", "Hat in the Cat", and "My cat is cool".

I'm not familiar with the text indexes feature but perhaps that is something
you could also look into. The above query would not benefit from any index
and would be slow unless you were dealing with a small table.

You realize this is not a C# question ... you might get better answers in a
SQL Server newsgroup.

--Bob
 
Tarrant,

I believe that this should have been posted to the
microsoft.public.sqlserver.programming group since this is a SQL question.
You will probably get a better response there.

Anyway...one way that you can accomplish this is to use Split UDF to
seperate the field into an array and then search the array for the word that
you seek. If you use this method you will have to use a UDF because Sql
Server has no array datatype. There are several Split UDFs at the link that I
have posted below.

Hope this helps.
 
Back
Top