CODEWITHSUNDEEP
sqlstringcontainssql-server-2012
Yatrix
Yatrix

Reputation: 13775

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.

Upvotes: 75

Views: 212244

Answers (4)

Sanjeev Singh
Sanjeev Singh

Reputation: 4066

    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.

Upvotes: 8

CD Jorgensen
CD Jorgensen

Reputation: 1391

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'

Upvotes: 30

podiluska
podiluska

Reputation: 51494

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

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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%'

Upvotes: 108

Related Questions