Use below query to get output

----NOTE I AM USING SQL 2008 R2

DECLARE @S VARCHAR(MAX)
SET @S = 'LP MEN'

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

----------------OUTPUT-----------------
--1	LP,Men,Shirt,Formal,40	LP Men Shirt Formal 40
--2	LP,Men,Pant,Formal,30	LP Men Pant Formal 30