Better way of matching a customer address
-
I'm trying to find a better way of matching an address. So I was thinking, the credit card processors use the street address 1 and zip or postal code to do a AVS match, and perhaps I should just copy that idea. I wrote this, sort of copied it and made some edits But I can't get it right. I can't figure out where to place my input to match against.
DECLARE @PostalCode VarChar(12), @CustomerID Int, @StreetAddress1 VarChar(80);
SET @PostalCode = '92648';
SET @CustomerID = 5;
SET @StreetAddress1 = '18751 Park Haven Lane';
SELECT *
FROM CUSTOMER_SHIPPING_ADDRESS
WHERE LEFT(@StreetAddress1, 5) IN (
SELECT LEFT(StreetAddress1, 5)
FROM CUSTOMER_SHIPPING_ADDRESS
GROUP BY LEFT(StreetAddress1, 5 )
HAVING COUNT(*) > 1
)
AND PostalCode = @PostalCode
AND CustomerID = @CustomerID -
I'm trying to find a better way of matching an address. So I was thinking, the credit card processors use the street address 1 and zip or postal code to do a AVS match, and perhaps I should just copy that idea. I wrote this, sort of copied it and made some edits But I can't get it right. I can't figure out where to place my input to match against.
DECLARE @PostalCode VarChar(12), @CustomerID Int, @StreetAddress1 VarChar(80);
SET @PostalCode = '92648';
SET @CustomerID = 5;
SET @StreetAddress1 = '18751 Park Haven Lane';
SELECT *
FROM CUSTOMER_SHIPPING_ADDRESS
WHERE LEFT(@StreetAddress1, 5) IN (
SELECT LEFT(StreetAddress1, 5)
FROM CUSTOMER_SHIPPING_ADDRESS
GROUP BY LEFT(StreetAddress1, 5 )
HAVING COUNT(*) > 1
)
AND PostalCode = @PostalCode
AND CustomerID = @CustomerIDOh I think I get it now, should of had a cup of coffee this morning
DECLARE @PostalCode VarChar(12), @CustomerID Int, @StreetAddress1 VarChar(80);
SET @PostalCode = '92648';
SET @CustomerID = 5;
SET @StreetAddress1 = '18751 Park Haven Lane';
SELECT *
FROM CUSTOMER_SHIPPING_ADDRESS
WHERE PostalCode = @PostalCode
AND CustomerID = @CustomerID
AND LEFT(StreetAddress1, 5) = LEFT(@StreetAddress1, 5) -
I'm trying to find a better way of matching an address. So I was thinking, the credit card processors use the street address 1 and zip or postal code to do a AVS match, and perhaps I should just copy that idea. I wrote this, sort of copied it and made some edits But I can't get it right. I can't figure out where to place my input to match against.
DECLARE @PostalCode VarChar(12), @CustomerID Int, @StreetAddress1 VarChar(80);
SET @PostalCode = '92648';
SET @CustomerID = 5;
SET @StreetAddress1 = '18751 Park Haven Lane';
SELECT *
FROM CUSTOMER_SHIPPING_ADDRESS
WHERE LEFT(@StreetAddress1, 5) IN (
SELECT LEFT(StreetAddress1, 5)
FROM CUSTOMER_SHIPPING_ADDRESS
GROUP BY LEFT(StreetAddress1, 5 )
HAVING COUNT(*) > 1
)
AND PostalCode = @PostalCode
AND CustomerID = @CustomerIDYou might also want to look into Levenshtein Distance. Plus, unless you are using an inferior database system, use
JOINs rather than subqueries andIN. -
You might also want to look into Levenshtein Distance. Plus, unless you are using an inferior database system, use
JOINs rather than subqueries andIN. -
I had to use Levenshtein a while back to match addresses from two systems. Some times there's no other way.
-
I had to use Levenshtein a while back to match addresses from two systems. Some times there's no other way.
-
I'm trying to find a better way of matching an address. So I was thinking, the credit card processors use the street address 1 and zip or postal code to do a AVS match, and perhaps I should just copy that idea. I wrote this, sort of copied it and made some edits But I can't get it right. I can't figure out where to place my input to match against.
DECLARE @PostalCode VarChar(12), @CustomerID Int, @StreetAddress1 VarChar(80);
SET @PostalCode = '92648';
SET @CustomerID = 5;
SET @StreetAddress1 = '18751 Park Haven Lane';
SELECT *
FROM CUSTOMER_SHIPPING_ADDRESS
WHERE LEFT(@StreetAddress1, 5) IN (
SELECT LEFT(StreetAddress1, 5)
FROM CUSTOMER_SHIPPING_ADDRESS
GROUP BY LEFT(StreetAddress1, 5 )
HAVING COUNT(*) > 1
)
AND PostalCode = @PostalCode
AND CustomerID = @CustomerIDjkirkerx wrote:
AVS match,
There are specific rules associated with that such as removing all punctuation, using abbreviations for standard names, etc. You can probably google for that that. However those are also intended to match with a credit card and the address. And not just the address. Without some other verification system I doubt just an address match is feasible.
-
jkirkerx wrote:
AVS match,
There are specific rules associated with that such as removing all punctuation, using abbreviations for standard names, etc. You can probably google for that that. However those are also intended to match with a credit card and the address. And not just the address. Without some other verification system I doubt just an address match is feasible.
I should of removed all the chars that are not A-Z a-z 0-9 first. Well so far so good, much better than before. I was creating a card record with the billing and shipping address. Worked fine for 10 years. The users on the back end complained about it. Users with no programming experience complained that if they wanted to ship to a different address, they had to create a new card record and enter all the information again. So I split everything up into separate records, and recorded the ID's of each record in the order record. But a problem surfaced with duplicate records, in which I did a horrible job in checking for duplicates. So checking a card record was easy, but checking a address record became more complicated.