Multiple LIKE statements in SQL

Question

I have a table containing information about retail stores. I have a list of retail chain names (WalMart, Target, Eatons, etc...) When the user selects one I basically run a query to find anything having to do with that chain.

SELECT * FROM stores WHERE store_name LIKE '%$chain%' ORDER BY store_name ASC

For example, if the user selects 'WalMart', the query would return anything with the word 'WalMart' in it's name (WalMart Scarborough, WalMart Supercenter Toronto, WalMart Distribution Center etc...).

But now I would like to give the user the ability to search through this list via a search text box. The way I usually do searches is like so:

SELECT * FROM stores WHERE store_name LIKE '%$user_input%' ORDER BY store_name ASC

But in this case the query will return ALL stores containing the user_input, not just WalMarts. If I type in Toronto I would like to see WalMart Supercenter Toronto, but will of course get Target Toronto etc....

How can I make it so that I'm looking for anything containing the user_input but also only within the WalMart subset. I would like to do this in a single query if possible. Can I use two LIKE statements like this?

Sorry, haven't tried anything yet as I'm not sure where to start.

Jack · Accepted Answer · 2013-12-27 19:11:42Z

8

Yes, you can do the following:

SELECT * FROM stores WHERE store_name LIKE '%$user_input%' 
AND store_name LIKE '%Walmart%'
ORDER BY store_name ASC

answered Dec 27, 2013 at 19:11

Jack

2,8802 gold badges30 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PaulG Over a year ago

Of course, what was I thinking?? I ran that through my head before and it didn't seem right for some reason. I'm completely off the ball today, back at work after Christmas vacation... thanks a lot!

Max · Accepted Answer · 2013-12-27 19:12:19Z

1

Sure, you can just add two LIKE clauses:

SELECT * FROM stores 
WHERE store_name LIKE '%$chain%' 
AND store_name LIKE '%$user_input%'
ORDER BY store_name ASC

answered Dec 27, 2013 at 19:12

Max

9,98911 gold badges61 silver badges89 bronze badges

Comments

MazBros · Accepted Answer · 2013-12-27 19:17:59Z

1

If you want it to be limited to a single store chain you go like that:

SELECT * FROM stores WHERE store_name LIKE '%Walmart%' AND store_name LIKE '%$user_input%'      ORDER BY store_name ASC

answered Dec 27, 2013 at 19:17

MazBros

967 bronze badges

Comments

Amir Keshavarz · Accepted Answer · 2013-12-27 19:13:15Z

0

This will work i think:

   SELECT * FROM stores WHERE store_name LIKE '%$chain%' AND store_name LIKE '%$user_input%'  ORDER BY store_name ASC

answered Dec 27, 2013 at 19:13

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

Comments

elbuild · Accepted Answer · 2013-12-27 19:15:00Z

0

Yes it's possible, just use two LIKE. I would use also LOWER (applying lower case on the user_input as well) to make the query case independent.

SELECT * FROM stores WHERE LOWER(store_name) LIKE '%$user_input%' 
AND LOWER(store_name) LIKE '%walmart%'
ORDER BY store_name ASC

answered Dec 27, 2013 at 19:15

elbuild

4,8894 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

Multiple LIKE statements in SQL

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related