query to combine Like and IN [Resolved]

Posted by Sriharim under Sql Server on 6/2/2015 | Points: 10 | Views : 377 | Status : [Member] | Replies : 4
In products table, category and description columns. Sometimes a product will mistakenly updated in wrong category. Example: Rice should be in Groceries category but it is in General category. Now i use below query to update the category


update products set category = 'Groceries' where description like '%egg%' or description like '%Dosa%'
or description like '%Curd%' or description like '%tomm%' or description like '%dal%' or
description like '%rice%' or description like '%sugar%' or description like '%oil%' or description like '%chilli%'
or description like '%milk%' or description like '%boost%'


Is there query to combine the LIKE and IN operator or is there any query to reduce above query length ? (like is mandatory for to search products)

---
Srihari



Responses

Posted by: Bandi on: 6/3/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
-- Create temporary table #TempMatchList and populate with all your search strings
CREATE TABLE #TempMatchList (Col VARCHAR(100))
INSERT #TempMatchList VALUES ('boost'),( 'milk'),('chilli'),('rice'), ('sugar'),('oil'), ('dal'), ('Dosa'), ('egg')

SELECT *
FROM Products
WHERE EXISTS
(
SELECT *
FROM #TempMatchList
WHERE Description like '%'+Col+'%'
)

UPDATE Products
SET Category = 'Groceries'
WHERE EXISTS
(
SELECT *
FROM #TempMatchList
WHERE Description like '%'+Col+'%'
)

SELECT * FROM Products


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 6/3/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
try this sample working for me. I assume this is your answer


Create Table Prod
(
Prodid int Primary key identity(1,1),
ProductName varchar(30),
Produpid int
)



Insert into Prod values('Mouse',1)
Insert into Prod values('Keyboard',2)
Insert into Prod values('Pc',3)
Insert into Prod values('Monitor',4)
Insert into Prod values('Pendrive',5)

Select * from Prod




Update Prod Set Produpid=101 where ProductName In('Mouse','Keyboard')
Update Prod Set Produpid=102 where ProductName In('Pc','Monitor','Pendrive')



Mark as Answer if its helpful to you

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 6/3/2015 [Member] Starter | Points: 25

Up
0
Down
like is must because sometimes spelling in description/product will not be correct and sometime in description will be like pendrive, pen drive, pendrive+memory

like is required to search items.

this is for SSIS package (Execut sql task)

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 6/3/2015 [Member] Starter | Points: 25

Up
0
Down
helpful.......thank you

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response