Hi, Joe and thanks for the reply.
Unfortunately, the addresses I'm dealing with come in all shapes and sizes,
making a single approach unproductive.
I have some addresses that are more regular, such as "104 East Street",
where "104" is the StreetNumber column data.
Another address might follow the patter, "No. 13, Marsh Oaks",
where "No. 13" is the StreetNumber column data.
Yet another variation might be "Rt. 4, Box 12",
where "Rt. 4" or perhaps "Rt. 4, Box 12" might be entered
as the StreetNumber column data.
Isaac's approach with the regex parsing comes closest to a solution.
It handles the first two examples, but would not be correct for the third.
I may have to run a combination of functions based the form of the
StreetNumber data to parse and sort the data.
The problem is the inconsistency in addressing: Rural vs Urban,
Single Homes vs Apartment Complexes...etc.
I'm not sure what the "perfect" solution would be...
It would be quite simple if I could get a sort on a varchar field
that would also sort the numbers contained in that field appropriately.
A sort on a varchar field does sort the numbers properly to a degree...
1,2,3,4,5,6,etc... but the sort will also put 10 before 2, instead of 2
first and
that's where the problem comes in. Close, but no cookie for that result...
Rick
> -----Original Message-----
> From: Joe Eugene [mailto:[EMAIL PROTECTED]
> Sent: Saturday, March 29, 2003 4:25 AM
> To: CF-Talk
> Subject: RE: Any wanna help? Trying to order output by the
> number that's
> part of a varchar field...
>
>
> > How would I select out the number, if present, in the
> street number field
>
> have you tried
>
> Order by right(StreetNo, len(StreetNo)-4)
>
> The above might help some but not accurate for all cases.
>
>
> Joe Eugene
>
>
> > -----Original Message-----
> > From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> > Sent: Friday, March 28, 2003 10:48 PM
> > To: CF-Talk
> > Subject: Any wanna help? Trying to order output by the
> number that's
> > part of a varchar field...
> >
> >
> > Hi, all...
> >
> > I'm trying to order street addresses.
> >
> > I originally setup the the first field in the address to
> hold the steet
> > number and made it numeric so it could be ordered.
> >
> > Unfortunately, I found out that some of the addresses had no
> > street number,
> > but were addresses like "No. 10 Magnolia Park", "No. 12
> Pecan Park", etc.
> >
> > "No. 10" is what's now going in the street number field,
> and, of course,
> > that field
> > cannot be numeric, but a character field. However, that's
> throwing off the
> > ordering
> > of the properties...
> >
> > How would I select out the number, if present, in the
> street number field
> > and have Cold Fusion order the query output by the number?
> >
> > Thanks for any help!
> >
> > Rick
> >
> >
> > Rick Faircloth
> > WhiteStoneMedia.com
> >
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
