Search on Varchar Field

[krotha]

Hi All
I would like to add the ability to run a search on a varchar(2500) column,
This column is a description field, I would like to be able to search the descriptions by
passing in something like 'Lumber OR Heavy AND Carpet' and return all the records that have the words
'Lumber' or 'Heavy' and 'Carpet' somewhere in the description field.

Thanks for any help.

 

[jackfl]

WHERE field1 LIKE '%Lumber%' OR (field1 LIKE '%Heavy%' AND field1 LIKE '%Carpet%')

 

[RobHudson]

Hey there!

Maybe making use of Full-Text queries might help you here??

Try looking in Books Online under the following to help you on your way:
full-text queries, CONTAINS
full-text queries, examples - choose - Example of Combining Full-text Administration and Full-text Query

Hope that helps!
Rob

 

[SQLSister]

Full text queries would be significantly faster than the like statement query because when you use a wildcard as the first character fo a search parameter you cannot use indexes.

 

[krotha]

Hi all thanks for your input.
I did some study and implemented full text indexing this works fine in most cases, but for some cases it gives error
Exec search '"lumber" and "Heavy" and "Carpet" and "2"'
Server: Msg 7619, Level 16, State 1, Procedure Search, Line 10
Execution of a full-text operation failed. A clause of the query contained only ignored words.

 

[SQLSister]

You could have it runthe like% query if it fails the full text query. Full text doesn't document every word; it has a list of words it ignores as being too common usually and, the and the like. I'm surprised it would ignore carpet and lumber though

 

[krotha]

It is not Ignoring carpet and lumber, upto Carpet is working
fine. when I add one more and to it i.e "2" it gives the error.

 

[krotha]

Some noise words are creating problem. I Edit noise.enu file in the MSSQL\FTDATA folder. Now It works fine.