72

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.

starball
  • 20,030
  • 7
  • 43
  • 238
Yatrix
  • 13,361
  • 16
  • 48
  • 78

4 Answers4

103

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%'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    ...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! – Yatrix Sep 04 '12 at 14:20
  • 3
    @Yatrix . . . you can also use charindex('thisstring', @stringVar), if the like doesn't seem to work. – Gordon Linoff Sep 04 '12 at 14:22
  • @Asad . . . Of course this will work. The wildcard `%` matches zero or more characters. – Gordon Linoff Aug 17 '14 at 12:25
29

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'
CD Jorgensen
  • 1,361
  • 9
  • 8
  • That's perfect if what you're looking for is one of the wild cards that the LIKE statement uses – Jrud Nov 27 '19 at 16:57
7

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

podiluska
  • 50,950
  • 7
  • 98
  • 104
7
    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.
Sanjeev Singh
  • 3,976
  • 3
  • 33
  • 38