Hi SajidHussa,

You need to separate the string on the basis of spaces between words and then use each word to loop through the table column and for this you need to make the Split function.

Refer below article link to make the function.

Split a String into Rows in SQL Server

Then you can use the function and write the below query.

Refer below sample.

SQL

CREATE TABLE #temp([ID] INT,[Name] VARCHAR(100)) 
INSERT INTO #temp VALUES(1,'best')
INSERT INTO #temp VALUES(2,'horlicks 500 gm')
INSERT INTO #temp VALUES(3,'1kg horlicks')

DECLARE @SearchName VARCHAR(200) = 'horlicks for kids', @i INT = 1 
CREATE TABLE #temp1([ID] INT,[Name] VARCHAR(100))
WHILE @i <= (SELECT COUNT(Item) FROM dbo.SplitString(@SearchName, ' '))
BEGIN
	DECLARE @Item VARCHAR(100)
	SELECT @Item = t.Item 
	FROM (SELECT Item,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) as R_Num 
		  FROM dbo.SplitString(@SearchName, ' ')) t 
	WHERE  t.R_Num = @i
	IF @Item <> ''
	BEGIN
		INSERT INTO #temp1
		SELECT * FROM #temp
		WHERE [Name] LIKE '%' + @Item + '%'
	END
	SET @i = @i + 1
END
SELECT DISTINCT * FROM #temp1
DROP TABLE #temp1
DROP TABLE #temp

Output