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

SqlStringContainsSql Server-2012

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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'

Solution 3 - Sql

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

Solution 4 - Sql

    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.
Categories
Recommended Sql Solutions
Recommended String Solutions
Recommended Contains Solutions
Recommended Sql Server-2012 Solutions

Previous Solution:

Capybara: Select an option by value not text

RubyCapybara

Next Solution:

does linux shell support list data structure?

LinuxBashShellAsh

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionYatrixView Question on Stackoverflow
Solution 1 - SqlGordon LinoffView Answer on Stackoverflow
Solution 2 - SqlCD JorgensenView Answer on Stackoverflow
Solution 3 - SqlpodiluskaView Answer on Stackoverflow
Solution 4 - SqlSanjeev SinghView Answer on Stackoverflow