[sql] How can I tell if a VARCHAR variable contains a substring?

I thought it was CONTAINS, but that's not working for me.

I'm looking to do this:

IF CONTAINS(@stringVar, 'thisstring')
   ...

I have to run one select or another, depending on whether that variable contains a string and I can't figure out how to get it to work. All the examples I'm seeing are using columns in the contains.

Thanks in advance.

Instead of LIKE (which does work as other commenters have suggested), you can alternatively use CHARINDEX:

declare @full varchar(100) = 'abcdefg'
declare @find varchar(100) = 'cde'
if (charindex(@find, @full) > 0)
    print 'exists'

CONTAINS is for a Full Text Indexed field - if not, then use LIKE

    IF CHARINDEX('TextToSearch',@TextWhereISearch, 0) > 0 => TEXT EXISTS

    IF PATINDEX('TextToSearch', @TextWhereISearch) > 0 => TEXT EXISTS

    Additionally we can also use LIKE but I usually don't use LIKE.