Check if a string contains a substring in SQL Server 2005, using a stored procedure

Question

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString.

How do I check if a string has a specific substring in SQL?

Solution 1

CHARINDEX() searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found

if CHARINDEX('ME',@mainString) > 0
begin
    --do something
end

Edit or from daniels answer, if you're wanting to find a word (and not subcomponents of words), your CHARINDEX call would look like:

CHARINDEX(' ME ',' ' + REPLACE(REPLACE(@mainString,',',' '),'.',' ') + ' ')

(Add more recursive REPLACE() calls for any other punctuation that may occur)

Solution 2

You can just use wildcards in the predicate (after IF, WHERE or ON):

@mainstring LIKE '%' + @substring + '%'

or in this specific case

' ' + @mainstring + ' ' LIKE '% ME[., ]%'

(Put the spaces in the quoted string if you're looking for the whole word, or leave them out if ME can be part of a bigger word).

Comments

[Damien_The_Unbeliever]

If you're looking for word matches (your second example), you'd need to a) add a space before and after @mainString (so you can match first or last word), and b) remove punctuation

[Nic]

s/recursive/nested/ -- "recursive" would be if REPLACE called itself; "nested" is when the result of a function call is immediately passed to another function.

[a.powell]

Would the 'ME' be case sensitive in SQL or would you also have to do an if statement for 'Me' and 'me'?

[Damien_The_Unbeliever]

@a.powell - depends on the collation's involved. You can always force it within this test if you need it one way or the other. E.g. compare select CHARINDEX('ME' collate Latin1_General_CS_AS,'Home') and select CHARINDEX('ME' collate Latin1_General_CI_AS,'Home'). (In collations, CS stands for Case Sensitive and I'm sure you can work out CI).

[E_Ri]

This is better than CHARINDEX() for me because in my particular case, I cannot execute the CHARINDEX() function due to limited permissions.

[Damien_The_Unbeliever]

@VincePanuccio - T-SQL's string processing is notoriously weak. SQL's strength is in set-based operations. In this case (wanting to do string processing, and something where a regex would be an obvious solution), it's more a case of them picking the wrong tool for the job.

[binarydreams]

(Just don't forget to prefix all your string constants with N if your column is an nvarchar, otherwise you get per-row conversions)

[The Red Pea]

This may be obvious, it seems like the LIKE operator supports RegEx, but LIKE operator is not RegEx per se; this technique uses wildcard characters as literals when it searches for '% ME[., ]%', the [., ] part is wildcard character group which checks for an optional period, comma, or space after the word "ME". Thanks!