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)asProductDescription,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 ...
Member
102 Points
952 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 15, 2019 09:46 AM|Ashraf007|LINK
Thank you ....
Users might search product in any given order ... We cannot set the column order here ...