SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: 'IN' query

  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    'IN' query

    Perhaps someone can help me with this SQL - I have a database full of UK sales properties. Each property row has a column value set to the UK-formatted property postcode, eg:

    SW1 4PJ

    To run a simlple partial postcode match such as a search for SW1 should find this example property the key SQL I have been using is:

    Code:
    (pr.ppostcode REGEXP '^SW1\[a-z\]' OR 
    pr.ppostcode REGEXP '^SW1 ')
    But the problem I now have is that the client wants the search to find a results set based on a list of multiple postcodes. So for example the list of multiple postcodes could be:

    SW1,W1, W11,SW14

    I need some fast SQL that will match the example SW1 4PJ property this example list of partial postcodes.

    I can't use

    Code:
    (pr.ppostcode1 IN ('SW1','W1', 'W11','SW14'))
    which would work if all the postcodes in the DB were split into 2 columns - postcodeA and postcodeB. They aren't so I have to make do with the DB I have. Any suggestions gratefully received :-)

  2. #2
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how many rows?

    if you are willing to try out the good 'ol LIKE "&#37;<search term>%" syntax...
    leo d.

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    London
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how many rows?
    1000s

    if you are willing to try out the good 'ol LIKE "%<search term>%" syntax...
    But it would be LIKE '%X%' OR LIKE '%Y%' ETC ETC ETC

    Doesn't seem very optimized. Am thinking of creating a temporary table that holds the SW1,W1, W11,SW14 partial postcodes. Then INNER JOINing this temporary table ON tempTable.Postcode LIKE SUBSTRING(pr.ppostcode,1,LOCATE(' ',pr.ppostcode)-1)

    Which I think should bring me back only matches...

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there's always a space after W15 or SW1 before the rest of the postal code right? Look into the use of SUBSTRING_INDEX and split that part of your postal code to match against your IN list. Don't permanently split the codes I mean but use it as part of the search.


Bookmarks

Posting Permissions

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