How to filter multiple columns based on single criteria in sql

Reply

Ashraf007

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Brand] [varchar](50) NULL,
    [Gender] [varchar](50) NULL,
    [Product] [varchar](50) NULL,
    [ProductType] [varchar](50) NULL,
    [Size] [float] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Test] ON
 
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (1, N'LP', N'Men', N'Shirt', N'Formal', 40)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (2, N'LP', N'Men', N'Pant', N'Formal', 30)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (3, N'Ceilo', N'Men', N'Shirt', N'Casual', 40)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (4, N'Ceilo', N'Men', N'Pant', N'Casual', 30)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (5, N'LP', N'Women', N'Shirt', N'Formal', 32)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (6, N'LP', N'Women', N'Pant', N'Formal', 28)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (7, N'Ceilo', N'Women', N'Shirt', N'Casual', 32)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (8, N'Ceilo', N'Women', N'Pant', N'Casual', 28)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (9, N'LP', N'Kids', N'Shirt', N'Formal', 14)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (10, N'LP', N'Kids', N'Pant', N'Formal', 28)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (11, N'Ceilo', N'Kids', N'Shirt', N'Casual', 14)
INSERT [dbo].[Test] ([ID], [Brand], [Gender], [Product], [ProductType], [Size]) VALUES (12, N'Ceilo', N'Kids', N'Pant', N'Casual', 28)
SET IDENTITY_INSERT [dbo].[Test] OFF

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 + '%'

Thanks In Advance ....

Reply

jzero

Member

749 Points

522 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

Reply

limno

All-Star

122925 Points

9926 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.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Reply

Ashraf007

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

limno

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.

Thanks ... Any example ...

Thanks In Advance ....

Reply

yrb.yogi

Star

10243 Points

2349 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

----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

Reply

Ashraf007

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

yrb.yogi

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

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+'%'

Thanks In Advance ....

Reply

Wei Zhang

Participant

1270 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 :

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

Reply

Ashraf007

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

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 ...

Thanks In Advance ....

Reply

Wei Zhang

Participant

1270 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.

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

How to filter multiple columns based on single criteria in sqlRSS

8 replies

Shortcuts

How to filter multiple columns based on single criteria in sql

Re: How to filter multiple columns based on single criteria in sql

Re: How to filter multiple columns based on single criteria in sql

Re: How to filter multiple columns based on single criteria in sql

limno

Re: How to filter multiple columns based on single criteria in sql

Re: How to filter multiple columns based on single criteria in sql

yrb.yogi

Re: How to filter multiple columns based on single criteria in sql

Re: How to filter multiple columns based on single criteria in sql

Wei Zhang

Re: How to filter multiple columns based on single criteria in sql