> 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.
Yea, you might use a float column and rewrite the regex to allow 2 numbers
wherein the 2nd number is a decimal value, this way rt. 4 Box 12 would
become 4.12 in the float column -- or use 2 columns to catch instances where
there's a 2nd number in the street address, since if you order 4.12 next to
4.5 and 4.1 they wouldn't be in what you'd expect to be a logical order.
Something like this might work to get a decimalled value
<cfset streetno =
rereplace(mystreet,"^[^0-9]*([0-9]+)[^0-9]*([0-9]*).*$","\1.\2","ALL")>
Though in an older version of cf server (pre-mx) it's probably better to use
refind() and mid()
<cfset myst = arraynew(1)>
<cfset st = refind(mystreet,"[^0-9]+",true)>
<cfloop index="x" from="1" to="#arraylen(st.pos)#">
<cfset arrayappend(myst,mid(mystreet,st.pos[x],st.len[x]))>
</cfloop>
Then just check your myst array for numbers... put as many number columns in
your table as you suspect you'll get in your results (I'd be damn surprised
by anything with more than 3) and just order the sql query on each of the
numeric columns in order.
> 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/uns
> ubscribe.cfm?user=633.558.4
s. isaac dealey 954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
tapestry api is opensource http://www.turnkey.to/tapi
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
