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?

score 437 · Accepted Answer · edited Feb 05 '21 at 13:44

437

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)

edited Feb 05 '21 at 13:44

bluish

answered Mar 24 '10 at 09:14

1

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. – Nic Apr 04 '17 at 20:05
2

Would the 'ME' be case sensitive in SQL or would you also have to do an if statement for 'Me' and 'me'? – a.powell Jun 01 '17 at 13:43
7

@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`). – Damien_The_Unbeliever Jun 01 '17 at 13:46
3

@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. – Damien_The_Unbeliever Sep 05 '17 at 05:24

Daniel Quinlan · Answer 2 · 2010-03-25T09:56:09.837

134

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

edited Mar 25 '10 at 09:56

answered Mar 24 '10 at 07:53

Daniel Quinlan

4

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 – Damien_The_Unbeliever Mar 24 '10 at 09:18
4

This is better than CHARINDEX() for me because in my particular case, I cannot execute the CHARINDEX() function due to limited permissions. – James T Snell Jun 27 '17 at 18:32
2

(Just don't forget to prefix all your string constants with `N` if your column is an `nvarchar`, otherwise you get per-row conversions) – Richard Szalay May 22 '18 at 01:43
This may be obvious, it seems like the `LIKE` operator supports RegEx, but `LIKE` operator is [*not* RegEx per se](https://stackoverflow.com/a/9076427/1175496); this technique [uses wildcard characters as literals](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#using-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! – Nate Anderson Jun 17 '21 at 17:42

2 Answers2