Comparing a search term that contains more than one word and searches different variations for the search term and pulls out records

If I have a search term that equals "Hello Connor Mcgregor", then how would I use this search term so that the search term loops each search word in the list for example:

searchTerm[0] = "Hello";
searchTerm[1] = "Conner"
searchTerm[2] = "Mcgregor"

and searches each value seperated by a space with a field or with two fields in the table and returns that row.

For the past few years I have been using EntityFramework but I have been trying this in LINQ and found that its better to use ADO.NET and use a stored procedure.

What is have tried:

    DECLARE @pos INT
    DECLARE @len INT
    DECLARE @value nvarchar(80)
    DECLARE @sql nvarchar(max)

    set @pos = 0
    set @len = 0

    WHILE CHARINDEX(' ', @searchTerm, @pos+1) > 0
    BEGIN
            set @len = CHARINDEX(',', @searchTerm, @pos+1) - @pos
            set @value = SUBSTRING(@searchTerm, @pos, @len)            

             if exists (select title, questionText from tblThread where title like @value + '%')
             BEGIN
                set @sql = (select title, questionText from tblThread where title like @value + '%')
                Print @sql
                --DO YOUR MAGIC HERE        
             END
            set @pos = CHARINDEX(',', @searchTerm, @pos+@len) +1
    END

I tried this but I can't seem to add the values in the temp table

ALTER PROCEDURE [dbo].[getThreadsBySearchTerm] 

    @searchTerm nvarchar(80)

AS
BEGIN
SET NOCOUNT ON;

    
    DECLARE @searchTermTbl TABLE (searchTerm nvarchar(max))
    DECLARE @value nvarchar(max)
    DECLARE @pos INT
    DECLARE @len INT
    DECLARE @sql nvarchar(max)

    DECLARE @sqlWithSpace Int

    set @pos = 0
    set @len = 0

    

    while CHARINDEX(' ', @searchTerm, @pos+1) > 0
    Begin
            set @len = CHARINDEX(' ', @searchTerm, @pos+1) - @pos
            set @value = SUBSTRING(@searchTerm, @pos, @len)            

            insert into @searchTermTbl values(@value)
            set @pos = CHARINDEX(' ', @searchTerm, @pos+@len) +1

    End 
     

     
    if exists(select * from @searchTermTbl)
        begin
            select title, questionText from tblThread
            inner join @searchTermTbl stt on tblThread.title Like '%' + stt.searchTerm + '%'
            inner join @searchTermTbl sttt on tblThread.questionText Like '%' + sttt.searchTerm + '%'
        end
    else
        begin
            select title, questionText from tblThread
            where title like '%' + @searchTerm + '%'
            or questionText like '%' + @searchTerm + '%'
        end

    select * from @searchTermTbl
END
GO


exec getThreadsBySearchTerm 'Question is'

its adding "Question" but not "is"

Solution

To populate your search string table, if you have a a single string like "'Hello There How Are You" that contains the words you want to search on. Then you can do the below and split on the " " space in the string. Or if you have a comma separated list, or any delimiter you can just change the Split string to use that instead.

The below example works. You can tweak it with your data and then do the JOIN like I suggested above:

DECLARE @SearchString VARCHAR(MAX)
DECLARE @SearchTable TABLE(SearchValue VARCHAR(MAX))

-- The string, list of strings you want to split to search on. 
SET @SearchString = 'Hello There How Are You'

-- split the search string and insert into the table.
INSERT INTO @SearchTable (SearchValue)
SELECT * FROM STRING_SPLIT(@SearchString, ' ')

-- just a select to test to ensure the string split worked
SELECT * FROM @SearchTable

-- Then this select should join your table (just update the table and field name
Select *
FROM TableA  A
INNER JOIN @SearchTable ST ON A.SearchTextFiled like '%' + ST.SearchValue + '%'