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?
Add a comment
|
2 Answers
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)
answered Mar 24, 2010 at 9:14
Damien_The_Unbeliever
241k28 gold badges358 silver badges470 bronze badges
4 Comments
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).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.
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).
5 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
James T Snell
This is better than CHARINDEX() for me because in my particular case, I cannot execute the CHARINDEX() function due to limited permissions.
Richard Szalay
(Just don't forget to prefix all your string constants with
N if your column is an nvarchar, otherwise you get per-row conversions)
Nate Anderson
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!
Klesun
Does not account for the possibility of
@substring having special characters like "%" or "_". I'm surprised nobody brought this up over 15 years...