Returning partial address matches and mismatch position using L2S or SQL

Question

I need to implement a method that takes an address split up into individual parts and returns any matching items from an address table. If no matches are found, I want to be able to return a value indicating where it failed. Each input param has a corresponding field in the table.

The signature would look something like this:

List<Address> MatchAddress(string zipCode, string streetName, string houseNumber, string houseLetter, string floor, string appartmentNo, out int mismatchPosition)
{
  // return matching addresses
  // if none found, return the position where it stopped matching
  // zipCode is position 0, appartmentNo is position 5
  //
  // an empty param value indicates "don't check"
}

I know I can construct the method such that I start with all the parameters, execute the query and then remove param by param (from the right side) until either a match is found or I run out of parameters, but can I construct a query that is more effective than that, i.e minimizing the number of calls to the db, maybe even as a single call?

Answer 1

here a good link to checkout which will give you several purely SQL methods to do this, with PROs and CONs of each method: Dynamic Search Conditions in T-SQL by Erland Sommarskog

Answer 2

I think you could get a solution in one query using case statements and some nested queries, but before I go there, I'm not sure I follow what you're trying to accomplish. Assume the following sample data set:

ID   House_Number    Street_Name   Zip_Code
=============================================
1            3012            Elm      10010
2             412            9th      10010
3             412           Main      90210
4             710           Main      60606

Also assume the following method call, passing in a zip, name and house number:

MatchAddress('10010', 'Main', '710')

What would you want to get back in this case? Your signature has a single out variable for the mismatch position, but this example would partially match all four of the records involved on at least one element. What would you want to return for record 1, for instance, where there's a match on zip code, but not on the house_number or street_name attributes?

======================================

OK, saw your comment. Here's a query that I think is on the right path for you. The WHERE clause is a series of ORs that return any record that matches on at least one of the criteria. Then the case statement looks to see where they're not equal to the passed in value. Obviously the least specific match is subjective, but you could re-order the case statement to put the desired match criteria in the order you want. I had this working on a MySQL db here.

SELECT address.*, CASE 
    WHEN zip_code <> '10010' THEN 'No match on Zip'
    WHEN street_name <> 'Elm' THEN 'No match on Street Name'
    WHEN house_number <> '29' THEN 'No match on House Number'
    ELSE 'Match on all elements'
  END AS zip_match
from address
where zip_code = '10010'
OR street_name = 'Elm'
OR house_number = '29'

I've made it return some pretty wordy statements for clarity here, but you could obviously have it return a number code or whatever makes sense for you. This also doesn't take into account issues like case sensitivity. As it's written, it's looking for precise case matching too. Depending on your db environment or how you're data is stored, you may need to address that if that's not what you want.