locked
How to filter multiple columns based on single criteria in sql RRS feed

  • Question

  • User1152553138 posted

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

    Friday, April 5, 2019 5:35 AM

All replies

  • User-943250815 posted

    It does not work as expected.
    Use a parameter per field also use Like properly https://www.w3schools.com/sql/sql_like.asp

    Friday, April 5, 2019 12:05 PM
  • User77042963 posted

    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.

    Friday, April 5, 2019 2:09 PM
  • User1152553138 posted

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

    Saturday, April 6, 2019 3:24 AM
  • User-2082239438 posted

    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

    Saturday, April 6, 2019 4:35 AM
  • User1152553138 posted

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

    Saturday, April 6, 2019 7:19 AM
  • User-1174608757 posted

    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

    Monday, April 8, 2019 4:15 AM
  • User1152553138 posted

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

    Monday, April 15, 2019 9:46 AM
  • User-1174608757 posted

    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

    Tuesday, April 16, 2019 5:51 AM