How can I tell if a VARCHAR variable contains a substring?

Question

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.

Corollary

Answer 1

The standard SQL way is to use like:

where @stringVar like '%thisstring%'

That is in a query statement. You can also do this in TSQL:

if @stringVar like '%thisstring%'

Ducky

...that makes me sad. I tried that the other day and it didn't work. I musta typed something wrong because it just worked. I'm chalking that up to doing something late on a Friday before a 3-day weekend. I may have typed the entire statement backwards for all I know. Thanks! – Corollary

@Corollary . . . you can also use charindex('thisstring', @stringVar), if the like doesn't seem to work. – Ducky

@Asad . . . Of course this will work. The wildcard % matches zero or more characters. – Ducky

Answer 2

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'

Eimile

That's perfect if what you're looking for is one of the wild cards that the LIKE statement uses – Adigun

Answer 3

    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.

Intestine

Agreed, CHARINDEX works perfectly while the use of LIKE is kind of unpredictable. For an example, for some reason LIKE fails to find '[' in a string while CHARINDEX perfectly finds it with no issues. – Soucy

Answer 4

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

Quechua