Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    8

    Unanswered: Multi-value "LIKE" Statement

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --

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

    to this --

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Have you left your NZDF flag permanently on Rudy?

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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))

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •