I need an advice on formalizing address

[Sam Sungshik Kong]

Hello!

My company has a database with customer information (SQL Server 7.0).
My boss wants me to formalize the address (USA address).

The street address format is like this (city, state, zip are not my
concern).
(I know that there will be exceptions and I will ignore them if I can
recognize them to be exceptional.)

street_number [direction] street_name [street_suffix] [suite_mark]
[suite_number]

For example:
123 N. Wilshire Blvd. # 707

Variations:
123 North Wilshire Blvd. Ste. 707
123 North Wilshire Boulevard Suite 707
....

(even, "North" can be name of street)

I think I need to analyze variation sets for different cases and use regex
to replace.

Does anyone have this kind of experience?

TIA.
Sam

 

[jim]

street_number [direction] street_name [street_suffix] [suite_mark]
[suite_number]

For example:
123 N. Wilshire Blvd. # 707

Variations:
123 North Wilshire Blvd. Ste. 707
123 North Wilshire Boulevard Suite 707
....

(even, "North" can be name of street)

Don't forget Utah adresses:

190 N 200 W
Orem, UT 12345

Usually misinterpreted as 190 N on 200 West Street.
But it could also be 200 W on 190 North Street.

The adresses are actually coordinates.

 

[Sam Sungshik Kong]

street_number [direction] street_name [street_suffix] [suite_mark]
[suite_number]

For example:
123 N. Wilshire Blvd. # 707

Variations:
123 North Wilshire Blvd. Ste. 707
123 North Wilshire Boulevard Suite 707
....

(even, "North" can be name of street)

Don't forget Utah adresses:

190 N 200 W
Orem, UT 12345

Usually misinterpreted as 190 N on 200 West Street.
But it could also be 200 W on 190 North Street.

The adresses are actually coordinates.

Hmmm. I've never seen such type of address.
I'll consider that.

Thanks...

Sam

 

[David G. Andersen]

Although one is a street and one is a street address.
You just don't know which. It's _likely_ that the more
round number is the street, but without a street guide,
you can't be 100% sure. For instance, an address like:

1105 E 4800 S

is probably on the street "4800 S"

But luckily, you don't need to worry about it unless you're
trying to map things, since the coordinate statement _is_
the canonical form of the address.

I miss coordinate addresses and a grid system.

-Dave

 

[Austin Ziegler]

Although one is a street and one is a street address.
You just don't know which. It's _likely_ that the more
round number is the street, but without a street guide,
you can't be 100% sure. For instance, an address like:

1105 E 4800 S

is probably on the street "4800 S"

But luckily, you don't need to worry about it unless you're
trying to map things, since the coordinate statement _is_
the canonical form of the address.

I miss coordinate addresses and a grid system.

Also common in Canada is this:

200-55 University St.

This is NOT 200 University St. #55, but 55 University St. #200.

-austin

 

[Aredridel]

Also common in Canada is this:

200-55 University St.

This is NOT 200 University St. #55, but 55 University St. #200.

Also, the annoying Brevé Ridgway address:

81432-0471

No box, no street, just zip+4. Translation is "Box 471", but that's
not neccesary and often not specified...

.. and some of the really rural places, it's

Name
(.*)
XXXXX Island, ST, 12345

Where the middle part doesn't matter at all, because everyone walks to
the post anyway.