Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: How do I filter fields that vary slightly?

    Hello! I am a real estate agent and MySQL newbie; trying to filter my leads against listings that are currently active (have relisted with another agent). To do this I have 2 tables, one named Actives and another named Expireds_New

    Using a stored procedure to filter these out:
    Delete
    From `expireds_new`
    WHERE `expireds_new`.`Address` IN
    (SELECT `Address`
    FROM actives)
    ;
    END

    My issue is, sometimes when a property is relisted the name can vary slightly. For example, today I just had one come off the market listed as XXXX Costa del Mar #602, and it was relisted as XXXX Costa del Mar Unit 602. I would like to filter these out, but I wouldn't want to filter out XXXX Costa del Mar #500 for example, so I am a little wary of using the LIKE clause. Is there a better way of doing this?

    Thanks in advance for any and all constructive help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are two obvious ways to filter leads like what you've described.

    One way is to use the ZIP+4 code from https://tools.usps.com/go/ZipLookupAction_input for the properties which will get near all of address variations. This is free and works for the vast majority of US Addresses, but it is difficult or impossible to automate.

    The other way is using a service such as https://www.melissadata.com/Lookups/addressverify.asp to standardize and manage your addresses. This is a service so you will eventually end up paying something for that service. It is easier to use and it can be easily automated.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side point when you have two tables which store essentially the same data like what you've described, that is generally a bad thing. If the only difference between the tables is that one table is active and the other is expired, then I would include two dates in each row. One date would show when the listing became active (which would also allow you to store pending listings), and one date which would show when the listing expired. The two columns make your table much more useful in the long run, and also allow you to combine active and expired (and pending) listings into a single table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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