charindex and substring

[ichervin]

Hi All,

I have an address field that has data that needs to parsed. The data has different formats. I have tried charindex and substring but I am not getting the results I need.

Here is what the data looks like:

Paper Mill Rd 3332
Allegheny Ave 13 1/2
Balto Nat'l Pike 6600 R-2
Rolling Rd N 3030


Here is the results I am looking for:

street dir street number street name street type street unit
3332 Paper Mill Rd
13 1/2 Allegheny Ave
6600 Balto Nat'l Pike R-2
N 3030 Rolling Rd

Can you help? Thanks.

[nosepicker]

I don't think this will be easy, and it won't involve just one SQL statement. For example, just to extract the street direction, you would probably have to do something like this just to consider all possible cases (I included a few other addresses to illustrate the different cases):

Main St. 4 S
Easy St. 4S
E 26th St 123


SELECT
SUBSTRING(address,
CASE WHEN PATINDEX('%[A-Za-z] %', address) = 1 THEN 1
WHEN PATINDEX('%[A-Za-z] %', REVERSE(address)) = 1 THEN LEN(address)
WHEN PATINDEX('% [A-Za-z] %', address) > 0 THEN PATINDEX('% [A-Za-z] %', address) +1
END,
1)
FROM YourTable


To get the other address parts will probably be even more complicated.

[MAK]

If address format is consistent, then you can do with a single query.

or if you have a set of rules that you can apply then you can do this in a single query.

[MAK]

please refer.

http://forums.databasejournal.com/sh...threadid=31654

[Rawhide]

How important is accuracy? If accuracy is critical, the I suggest using some third party software that specializes in this. I use ZP4 software.

I also wrote a function a long time ago that is fairly accurate. I'll post it later when I get home.

[Rawhide]

The function I wrote a long time ago is attached.