Searching for a word in a string

[kgomotso]

How do I search for a word in a string without using %like% because %like% only finds a string that begins with the word and not that actually has an occurance of the word(it could be in the middle of the string)

 

[Cagliostro]

charindex(ColumnName,'what you search') John Fill
ivfmd@mail.md

 

[ScottAW]

CharIndex will actually return a number -- that number will be the starting position in the string for the text you are searching for. If you SELECT statement states "WHERE FieldName Like '%searchstring%' then you should get matches whether the searchstring is at the beginning, end, or anywhere inside the string. The only way you would only get matches that start with searchstring is if you left off the wildcard at the beginning (i.e. WHERE FieldName Like 'searchstring%').