Compare two SUBSTRINGS in SQL Server

Question

I have the following test code:

DECLARE
     @Str1 VARCHAR(MAX) = 'Hello World'
    ,@Str2 VARCHAR(MAX) = 'World Hello'

SELECT CHARINDEX(@Str1, @Str2)

The select statement returns zero because it takes the whole @Str1 and tries to find it within @Str2.

How can I make the search compare sub-strings?

In other word, I want the search to see if a substring of @Str1 can be found as substring in @Str2

Answer 1

If you're just splitting on spaces what you'd do is split the string and then search for each split word and get the char index of that.

Here's a quick example:

DECLARE
     @Str1 VARCHAR(MAX) = 'Hello World'
    ,@Str2 VARCHAR(MAX) = 'World Hello'


   DECLARE @substring VARCHAR(MAX)
   DECLARE c CURSOR FOR 
   SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@Str1, ' ', '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
    OPEN c
    FETCH NEXT FROM c INTO @substring
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT CHARINDEX(@substring, @str2)
    FETCH NEXT FROM c INTO @substring
    END
    CLOSE c
    DEALLOCATE c