Wei Zhang

Hi,ASHRAF007

According to your code, firstly,in the answer of yrb.yogi, 'LP MEN'  is  the combination of Brand and Gender which has the same order as the querstring :

Brand+' '+Gender+' '+Product+' '+ProductType+' '+CONVERT(VARCHAR,Size) AS SerchProduct

However you set the @S = 'LP Shirt',which corresponds to the Brand and product columns in your table ,so if you want to get the output, you should set the SerchProduct as the same oder as below:

Brand+' '+Product+' '+Gender+' '+ProductType+' '+CONVERT(VARCHAR,Size) AS SerchProduct

Here is my demo, I hope it could help you.

DECLARE @S VARCHAR(MAX)
SET @S = 'LP 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+'%'

output:

Best Regards

Wei

Thank you ....

Users might search product in any given order ... We cannot set the column order here ...