Hi Ashraf007,

According to your description, so, if we couldn't set the column order here , I suggest that we could divide the search into two parts and execute like query.For example, 'LP Shirt', we could firstly search like 'LP' part and then we could search for 'shirt part'. Here is the demo, I hope it could help you.

DECLARE @S VARCHAR(MAX)
DECLARE @T VARCHAR(MAX)
SET @S = 'LP Shirt'
SET @T = 'Shirt'
;WITH CTE AS
(
	SELECT DISTINCT ID, 
	Brand+','+Gender+','+Product+','+ProductType+','+CONVERT(VARCHAR,Size) as ProductDescription,
	Brand+' '+Product+' '+Gender+' '+ProductType+' '+CONVERT(VARCHAR,Size) AS SerchProduct
	FROM [dbo].[Test]
)
SELECT *
FROM CTE
WHERE SerchProduct LIKE '%'+@S+'%'AND SerchProduct LIKE '%'+@T+'%'

Best Regards

Wei