Hi I'm writing a search query where I am trying to check a large field (varchar (4000)) for specific words. As I understand it I can't use Full text indexing as I can't index the field. Oh SQL Server 2005 by the way!
I want to look for individual words including plural. This doesn't have to be too sophisticated so I am treating any punctuation as a space. Currently for on a search for 'August' I have
OR [SearchField] Like 'August[^a-z]%'
OR [SearchField] Like '%[^a-z]August'
OR [SearchField] Like '%[^a-z]August[s][^a-z]%'
OR [SearchField] Like 'August[s][^a-z]%'))
This is doing exactly what I want but I am allowing multiple words and treating each word as an or so the search is very slow when there are 4 or 5 words. Can anyone recommend a better use of pattern matching criteria to achieve the same ends ? Or possibly an alternative strategy.