Search for a word in a Sentence SQL-CodePudding

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';

CodePudding user response：

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

CodePudding user response：

Another idea you could try is to check the lengths after removing string1 from string2 which won't rely on any word boundary:

select iif(Len(@string2) - Len(@string1) 
           = Len(Replace(@string2, @string1, '')), 1, 0) IsSubstring;