I tried concatenate too ... See the below query its not working ...
Declare @S varchar(max)
Set @S = 'LP Men'
Select Distinct ID, CONCAT( Brand,',', Gender ,',', Product ,',', ProductType ,',', Size ) as ProductDescription FROM [dbo].[Test]
WHERE
Brand like '%' + @S + '%' OR
Gender like '%' + @S + '%' OR
Product like '%' + @S + '%' OR
ProductType like '%' + @S + '%' OR
Size like '%' + @S + '%'
----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
Yogesh Bhadauriya
Sr Technical Lead
Ahmedabad, India
----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
When i tried with other column the output is empty ... See below
DECLARE @S VARCHAR(MAX)
SET @S = 'LP Shirt'
;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+'%'
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+'%'
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 ...
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+'%'
Member
102 Points
952 Posts
How to filter multiple columns based on single criteria in sql
Apr 05, 2019 05:35 AM|Ashraf007|LINK
Eg: SEARCH TERM: Mens Casual Shirts
In my table this three will be in 3 different column or maybe different table.
See below table detail
I tried concatenate too ... See the below query its not working ...
Participant
1061 Points
669 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 05, 2019 12:05 PM|jzero|LINK
It does not work as expected.
Use a parameter per field also use Like properly https://www.w3schools.com/sql/sql_like.asp
All-Star
123252 Points
10024 Posts
Moderator
Re: How to filter multiple columns based on single criteria in sql
Apr 05, 2019 02:09 PM|limno|LINK
You need to split your mixed content of your input criteria before you search.
A better way is to use table-valued parameter to pass multiple values to a stored procedure.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
102 Points
952 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 06, 2019 03:24 AM|Ashraf007|LINK
Thanks ... Any example ...
Star
10303 Points
2360 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 06, 2019 04:35 AM|yrb.yogi|LINK
Use below query to get output
Sr Technical Lead
Ahmedabad, India
Member
102 Points
952 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 06, 2019 07:19 AM|Ashraf007|LINK
When i tried with other column the output is empty ... See below
Participant
1300 Points
522 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 08, 2019 04:15 AM|Wei Zhang|LINK
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 :
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.
output:

Best Regards
Wei
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 ...
Participant
1300 Points
522 Posts
Re: How to filter multiple columns based on single criteria in sql
Apr 16, 2019 05:51 AM|Wei Zhang|LINK
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.
Best Regards
Wei