finding a street name in a complete address

[ConnieHull]

Ok so I know that this is a old post... but I've got a question along these lines...

Our db was set up to have a billing and shipping address, the street address being one field. ie. "245 S. Lincoln St. Apt #B". We ship our products mostly in the US and Canada, but there are the occasions when we ship internationally as well. So now we have a customer file with over 15,000 records in it all with the one field Street Address (ok 2, Billing Street Address and Shipping Street Address).

My problem is arising because some states in the US are going to a Sales tax rate charge by destination, both of the 2 states where we currently collect sales and use tax are either already doing it or are going to it soon. One of the states is giving out a file that can be uploaded into FM to be searched to give the correct tax location code which in turn will give you the correct tax rate (from another table).

So my issue is this, if my customer gives me "245 S. Lincoln St. Apt #B" and that is how it is entered, how can I search the Location code table with it?

Here is a list that shows the location code table field descriptions.

Descriptions of the database fields and definitions for the Sales Tax GIS address data:

ADDR_LOW -Beginning address range

ADDR_HIG -Ending address range

ODD_EVEN -Left/right street side indicator

STREET -Street name

STATE -State indicator

ZIP -Five-digit zip code

PLUS4 -Four digit zip extension

PERIOD -Time period data is in effect

CODE -Location code

RTA -Regional Sound Transit indicator.

PTBA_NAME -Name of the Public Transportation Benefit Area

CEZ_NAME -Name of the Community Empowerment Zone

If I use the find and look for an address in the location code table and put in the appropriate information in the appropriate fields I get the results that I'm looking for, but I can't seem to figure out how to parcel up the street address into the needed pieces. One example is if I enter S. Arthur, there are no results... South Arthur, no results... Arthur, returns S Arthur St for the given other find info.

I need to be able to have this done automatically so that billing invoices won't be an all day task.

Any help would be appreciated, and unless I absolutely have to I would rather not go to each customer and re-enter the data so that the street name is it's own field.

TIA, Connie

Edited January 10, 2008 by Guest

[David Jondreau]

This really should be its own thread. Perhaps a moderator will make it so.

If what you're describing is really what you need, you've got your work cut out for you. The variety of actual address formats and the data entry issues make a simple solution impossible I would guess. I mean, some addresses in rural areas still don't have house numbering, so how can the GIS table find that as a range?

What taxes are assessed on a GIS level? I can only think of city and state sales and use taxes. Wouldn't a city/Zip combo be enough to get the info you need?

Maybe you could push the address to the web viewer and have that return the format you need via usps.com or the state tax office.

[ConnieHull]

I think that maybe you are right that it needs it's own post. And since I'm not sure how to send the data to USPS or the Tax lookup's web sites I'm lost on that as well. (I am working in 8)

The 2 states I'm dealing with WA and SC both have their own way of doing it, but with WA (the GIS) they charge by City/County, but not every city is in one county some are in multiple counties and of course there are multiple zip codes in each city, so unfortunately a city/zip search wont work.

If the moderator doesn't move this or I don't get any other answers I will post it on it's own.

Thanks, Connie

Edited January 10, 2008 by Guest

[Lee Smith]

I will move it, but tell me what the Subject should be, and what Topic Area you think it should be in first.

Lee

[ConnieHull]

Thanks Lee...

As far as where I'm not sure... maybe this same area?

As far as a subject maybe "finding a street name in a complete address?"

Thanks, Connie :)

[ConnieHull]

Ok, just to let everyone know... I've given up on trying to figure out how to dissect the Address field. I found a easier way of tracking down the information I was after. Matching Zip+4 fields. So far it seems to be pulling the correct info that I need.