Check if a string contains a substring in SQL Server 2005 Stored Procedure

Question

Hello

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

So how to check if a string has a substring in sql?

Thank you

Regards NLV

Answer 1

You don't say which flavor of SQL (ie; which database) but in Oracle you can use instr() and in SQL Server you can use SUBSTRING()

Answer 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).

Answer 3

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