Using Contains in Select Statement

Hi,



The following query:







gives me the following error:



Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'ttdThesisInfo' because it is not full-text indexed.



Any ideas as to why?

Well I guess you need to have a full text index, but... you could try a LIKE query instead.



How about this:

This finds any title with that three letter string.

Thank you for your suggestion, however the like statement doesn't work in this case because in reality I am passing a search word through a paramater and a common search term is "IED", which if I were to use a like statement it would return everything that included "ied" in the word. Where as I would really want to have it return where IED was an actual word.

Well, you could change it like so:

This would find any string where a the letters LED are separated by a space afterward. It would find words ending in LED. If that is a concern add a space to the front of the search string as well in which case it would find words where there is a space before and after which eliminates words ending in LED. Charindex is a good search tool as well, but if you are needing to ensure that your string represents a full word, I would stick with LIKE.

Actually that doesn't help. I need to search a string (can be at the beginning of the string, can be at the end, or even the middle). The string just has to CONTAIN the word. I am pretty sure that I need to use CONTAINS, but I need some help on the error I am receiving.

I think below query will do what you are looking for:

Note a space character before and after 'LED'.

This is still not what I want to do. I can not use a statement like this because it would not return a result if the keyword is the start of the string. Say I have a string "LED is something that....." It wouldn't return that as a result because LED is the begining of the string. Or what it if it was the end of the string...wouldn't return it either. I am 99% sure I don't want to use a like statement and want to use a contains statement.

http://msdn2.microsoft.com/en-us/library/ms178026.aspx



Here's the msdn link to the CONTAINS() function.



Looks like a PITA, IMO. I've never used it, I don't like full-text indexing (not that it is mentioned in the MSDN reference above).



And it does the exact same thing as LIKE anyways. It searches a string for a given string, with no prejudice given for or against whitespace.



You can refine your LIKE statement a bit though:



The above might be a bit slow, but it will eliminate situations where "IED" occurs in the middle of a word, but it will still pick up sentences beginning with IED or ending with IED.





ETA: Unless you are specifically using XQueries against an XML datatype (in other words, NOT a SQL table), Contains() is not a valid function to use in normal T-SQL queries anyways. At least that's how I read the MSDN article on it. Someone correct me if I am wrong.

Thank you!!!!! You lead me in the right direction, it's actually should be



SELECT *

FROM ttdThesisInfo

WHERE (Title like '%[^a-z]IED[^a-z]%')





And it works! Perfect, thank you!

What's the carrot symbol (^) do?



(I'm too lazy to find it in BoL)