Handy little nugget to check if a string exists within another string. Particularly useful if you’re working with a delimited string as I was, as it makes dealing with the delimiters irrelevant.
SELECT * FROM table WHERE CHARINDEX('string1', 'string2') != 0
In this instance ‘string1’ is the string you’re searching for in ‘string2’. Obviously these could be substituted for columns in a table or whatever.