Search for a word in a Sentence SQL

In SQL Server, I have two strings. Need to check if string1 is a substring of string2. It should exactly match a word in the sentence.

String2: El Alfi Abdullah Ahmed Abdullah

  • Match Scenario: String1: Ahmed
  • No match Scenario: String1: Ahme
declare @string2 varchar(max) = 'El Alfi Abdullah Ahmed Abdullah';
declare @string1 varchar(max) = 'Ahmed';

>Solution :

Here is one option using string_split(). However, this does NOT account for punctuation and such

declare @string2 varchar(max) = 'El Alfi Abdullah Ahmed Abdullah';
declare @string1 varchar(max) = 'Ahmed';

Select hits = count(*)
 From string_split(@string2,' ')
 Where value = @string1

Results

hits
1

Now, if @string1 was Ahme … the hits would be 0

Or if you want some simple string manipulation

Select sign(charindex(' '+@string1+' ',' '+@string2+' '))  -- returns 1 or 0

Leave a Reply