Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    24

    Unanswered: Getting results from 'vague' query

    Hi, I'm working with a database that has some sketchy data in it, i.e. the provinces are ON, Ontario, ONT, etc. I know that I could grab the 2 leftmost letters and search that way, but in other records they are not as distinct (i.e., Manitoba, MAN, MB).

    Is there a decent way to create a recordset based on several spellings of a name? Either this is just too simple and I'm glazing over something, or it's the frontend software I'm using (Dreamweaver MX).

  2. #2
    Join Date
    Jun 2003
    Location
    Binghamton, NY
    Posts
    8
    What is your query, you could do something like this:

    SELECT * FROM table
    WHERE province in ('Manitoba','MAN','MB');

    or to handle the case issue:

    SELECT * FROM table
    WHERE lower(province) in ('manitoba','man','mb');

    Now if you need to do more complex it might be beneficial to create a "crosswalk" table that you could JOIN against to get a consistent dataset. For example

    SELECT a.*, b.province_code
    from table a, crosswalk b
    where a.province = b.province;

    In the crosswalk table you'd have two fields, province_code and province.

    [province_code] | [province]
    MB | Manitoba
    MB | MAN
    MB | MB

    and so on.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    scott, great idea, i was gonna suggest exactly the same thing, a separate table -- every time you find another way something is spelled, just add a new entry to the table

    where did the name "crosswalk" come from?

    rudy
    http://r937.com/

  4. #4
    Join Date
    Oct 2002
    Posts
    24
    Excellent help thanks Scott! It took me a while to get the correct quotes etc because it constantly gave me errors, but your code was dead on. I'm more accustomed to using ASP and SQL/Access, so this PHP thing has me at a disadvantage. Dreamweaver is really bad at pinpointing errors too, so it took a little longer.

    Thanks!

Posting Permissions

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