Multi-value "LIKE" Statement

smack78 · #1 (**permalink**) 02-24-08, 16:17

I looked through Google but I couldn't find an answer. I'm sure this is a fairly simple question.

I was wondering if there was a way to do a statement with several LIKE values.

Example:

SELECT DISTINCT state_name
FROM `zip_code`
WHERE zip_code LIKE ('010%', '120%', '481%')

Here I have a table with zip codes and state names (obviously). I want to get the state names for zip codes that start with 3 specific digits.

Any one have a solution?

Thank you,
Smack

mike_bike_kite · #2 (**permalink**) 02-24-08, 17:06

Try

Code:

SELECT DISTINCT state_name
FROM `zip_code`
WHERE zip_code LIKE '010%'
         or  zip_code LIKE  '120%'
         or zip_code LIKE '481%'

There are loads of tutorials on SQL on the web.

Mike

r937 · #3 (**permalink**) 02-24-08, 20:57

change this --

WHERE zip_code LIKE ('010%', '120%', '481%')

to this --

WHERE left(zip_code,3) IN ('010', '120', '481')

mike_bike_kite · #4 (**permalink**) 02-25-08, 03:52

Have you left your NZDF flag permanently on Rudy?

aschk · #5 (**permalink**) 02-25-08, 04:41

OOh i like that solution rudy, what's the performance impact between the 2 (i'm not expecting statistics, just an educated guess at the difference and why)?

p.s. would it make sense to have an index on the first 3 letters of that column ?

Code:

CREATE INDEX partial_zip ON zip_code (zip_code(3))

r937 · #6 (**permalink**) 02-25-08, 07:30

i would just index the column

i think i read somewhere that mysql is optimized so that LEFT(xxx,n) will use an index just like LIKE will if there is no leading wildcard

Internet Services

Web Development

Digital Marketing

Consumer Tech