From: tom7744.no.spam@cox.net
On Wed, 26 Aug 2009 22:07:36 -0700, JG wrote:
I wholehartedly agree.
AT THE VERY LEAST you need to approach this systematically: before you
write code, write down the exact rules you will be implementing. Get
buy-in from the project sponsors. Implement them. Report back on which
addresses did not conform to the rules. Iterate as needed.
-Tom.
Microsoft Access MVP
>In article , emurphy42
>@socal.rr.com says...
>> Dave Clarke wrote:
>>
>> > I've been trying to use PATINDEX in SQL Server CE and it isn't
>> > behaving the way I would like it to. I'm trying to strip leading
>> > characters from a street address to return a meaningful street name.
>> >
>> > e.g. DISTINCT PATINDEX('%[^0-9, ]%', Address1)
>> >
>> > For addresses like '123 Some Street' this works in SQL Server but not
>> > in CE. I'm currently doing this processing using C# to get a list of
>> > distinct streets and I was hoping to gain some speedup by getting the
>> > database to do the heavy lifting. Any suggestions re alternative
>> > approaches would be much appreciated.
>>
>> case
>> when Address1 like '[^0-9 ]%' then 1
>> when Address1 like '_[^0-9 ]%' then 2
>> when Address1 like '__[^0-9 ]%' then 3
>> -- extend this pattern as far as needed in practice
>> end
>>
>That's reasonable, but there's really no substitute for an address
>verification (not just parsing) system that has a database of the actual
>addresses in a region, which vary a lot more than the rules we dream up.
>In Wisconsin (and other places, too?) rural addresses may be "W1234 Hwy
>X", not to mention the other hundreds of exceptions to the more obvious
>rules I have seen. If you are after accuracy approaching 100%, don't do
>it yourself, rather pass it off to one of the verification systems.
>They won't get 100% either, but at least that's their specialty.
>
>Jim Geissman (who has been burned in this)
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
