Many of them are on the same street. And if you want to
group the addresses by StreetName and order the addresses within that
street name grouping, well, then there's relevance, and it has to be done
like that.
These are lists of rental properties and many
are identified only as "No. 1 Magnolia Apartments", "No. 2 Magnolia
Apartments",
or something similar.
The "No. 1" and "No. 2" parts of the above examples are the StreetNumber
field data
and because of the "No." part, they have to be varchar fields.
Your solution follows what I thought might be needed, but fills in gaps in
logic for me...
- Run Initial Query
- Create Array containing only numbers from StreetNumber field using Regular
Expression
- Add colum to query with array data (this I wasn't familiar with)
- Run second query ordering by added column (and by StreetName)
- Reassign intial query values to "temp" query values
I'm running CF 4.5.2, so I don't have Query of Query capability.
Is that what's happening when this code is run?:
> <cfquery name="temp">
> SELECT * FROM myq ORDER BY streetno2
> </cfquery>
When you write:
> Probably have to actually insert the rereplace value into a
> db column tho actually...
Why would that be necessary? Would I have to rerun the regex "ordering
code"
to resort the properties in an "OrderNumber" column everytime a property was
added,
updated, or deleted from the database?
Could I somehow use one query that contains a regex to parse the
StreetNumber data?
Something like:
<CFQUERY Name="GetProperties"
Select *
from properties
order by val(rereplace(StreetNumber, "[^0-9]","",All")))
</CFQUERY>
Doesn't seem like that would work, because the "order by" would be looking
for a colum name...right? That would be the reason for the insertion of the
data
into an array first, right? Anyway to make that "One Query" solution work?
Thanks for the help and insights...
Rick
> -----Original Message-----
> From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 28, 2003 11:20 PM
> To: CF-Talk
> Subject: Re: Any wanna help? Trying to order output by the
> number that's
> part of a varchar field...
>
>
> I dunno why on earth you would want to order addresses by
> street number --
> unless they're _all_ on the same street, there's no relevance...
>
> However...
>
> <cfset temp = arraynew(1)>
>
> <cfloop query="myq">
> <cfset
> arrayappend(temp,val(rereplace(myq.streetno,"[^0-9]","","ALL")))>
> </cfloop>
>
> <cfset queryaddcolumn(myq,"streetno2",temp)>
>
> <cfquery name="temp">
> SELECT * FROM myq ORDER BY streetno2
> </cfquery>
>
> <cfset myq = temp>
>
> try that...
>
> Probably have to actually insert the rereplace value into a
> db column tho
> actually...
>
> > 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
> > This list and all House of Fusion resources hosted by
> > CFHosting.com. The place for dependable ColdFusion
> > Hosting.
>
> > 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
