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

sql-server string tsql stored-procedures sql-server-2005
NLV picture NLV · Mar 24, 2010 · Viewed 666.3k times · Source

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?

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Mar 24, 2010

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

Related questions

How do I remove the first characters of a specific column in a table?

In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for …

Read More
Find index of last occurrence of a sub-string using T-SQL

Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET System.String.LastIndexOf method provides. A little …

Read More
Better techniques for trimming leading zeros in SQL Server?

I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to …

Read More