Re: Any wanna help? Trying to order output by the number that's part of a varchar field...
On 3/29/03, Tim Plummer penned: If I change the above to POST method and use form.state to pass variable, it does pass multiple values, but I would like to know how to make the GET method pass multiple values. As far as I know, you can't. Best you can do is loop through the cgi.querystring and build your results that way. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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/unsubscribe.cfm?user=89.70.4
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=subscribeforumid=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/unsubscribe.cfm?user=89.70.4
RE: Any wanna help? Trying to order output by the number that's part of a varchar field...
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=subscribeforumid=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
RE: Any wanna help? Trying to order output by the number that's part of a varchar field...
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=subscribeforumid=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 dealey954-776-0046 new epoch http://www.turnkey.to lead architect,
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. You need to replace all the numbers with a fixed length zero filled number. You could do it like this: cfset zeroes[1] = '0' cfset zeroes[2] = '' cfset zeroes[3] = '000' cfset zeroes[4] = '00' cfset zeroes[5] = '0' cfset zeroes[6] = '' reReplace(address, ([0-9]+), zeroes[len(\1)] \1, ALL ) except a coldFusion bug prevents this from working (len always returns 2). So you'll have to do 5 different reReplaces, one for each length of number smaller than 6 (assuming 6 is big enough for your data). Here's some code that will do the trick (tested on CFMX): cffunction name=make6digits !--- convert the number to a 6 digit number --- cfset var d6n = arguments[1] cfset d6n = reReplace(d6n, (\b|\D)(\d\d\d\d\d)(\b|\D), \1\0\2\3, ALL) cfset d6n = reReplace(d6n, (\b|\D)(\d\d\d\d)(\b|\D), \1\00\2\3, ALL) cfset d6n = reReplace(d6n, (\b|\D)(\d\d\d)(\b|\D), \1\000\2\3, ALL) cfset d6n = reReplace(d6n, (\b|\D)(\d\d)(\b|\D), \1\\2\3, ALL) cfset d6n = reReplace(d6n, (\b|\D)(\d)(\b|\D), \1\0\2\3, ALL) cfreturn d6n /cffunction !--- create some test data --- cfset rsAddress = queryNew(address,addressKey) cfset queryAddRow(rsAddress) cfset queryAddRow(rsAddress) cfset rsAddress.address[1] = '4533 Banana Drive, Apt ##10, 36 Westminster, CO 80535-3716' cfset rsAddress.address[2] = '4533 Banana Drive, Apt ##4, 36 Westminster, CO 80535-3716' cfdump var=#rsAddress# label=the raw data !--- make a sort key for every address --- cfloop query=rsAddress cfset rsAddress.addressKey[currentRow] = make6digits(address) /cfloop !--- order by the sort key! --- cfquery name=rsAddressSorted dbtype=query SELECT * FROM rsAddress ORDER BY addressKey /cfquery cfdump var=#rsAddressSorted# label=the cooked data ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Any wanna help? Trying to order output by the number that's part of a varchar field...
Hi I was wondering if someone could help me with the following problem. I am having trouble passing multiple values using the FORM Method=GET to ColdFusion. The code below only passes one value for state, not multiple values: !-- expt.html -- html head /head body form action=expt.cfm method=GET name=form_1 select name=State multiple option value=NSW selectedNew South Wales option value=VICVictoria option value=QLDQueensland option value=SASouth Australia option value=WAWestern Australia option value=TASTasmania /select input type=SUBMIT VALUE+SUBMIT /form /body /html !-- expt.cfm -- html head /head body CFSET State=url.state cfoutput State = #State# /cfoutput /body /html If I change the above to POST method and use form.state to pass variable, it does pass multiple values, but I would like to know how to make the GET method pass multiple values. regards Tuum ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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=subscribeforumid=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/unsubscribe.cfm?user=89.70.4
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=subscribeforumid=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 dealey954-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=subscribeforumid=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
RE: Any wanna help? Trying to order output by the number that's part of a varchar field...
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=subscribeforumid=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 dealey954-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:
RE: Any wanna help? Trying to order output by the number that's part of a varchar field...
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. snip Well... okay... guess I was wrong. :) 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 That's what it was supposed to be but I left out dbtype=query from the cfquery attributes. 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? No, because you don't actually want an ordernumber column -- what you want is a numeric streetnumber column, so rather than getting the values and storing them in the db like this: streetno order 101 502 5380 3 16673 4 You would scrap the order column and just insert the streetno value as an integer value -- this allows you to only need to run the regex once before inserting the value into the db and then use the value returned from regex as the sort order for your query. Updates and deletes don't bother you because nothing (asside from the individual unit being added or updated) has to be recalculated. and the Val() ensures that any street address string not containing a number is given the value 0. 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? I know there's at least one 3rd party engine that supposedly allows regular expressions to be used in SQL Server, but by and large you can't use regex in sql queries. If regex were commonly allowed in sql it's conceivable that you could order by a regex value (I think you can order by other calculated values), however, you wouldn't want to do it that way anyhow because it's a lot of extra overhead (regex can be expensive, especially when you're talking about using it several hundred or thousand times consecutively). So you'd really want to perform the regex at time of insert/update anyway. Thanks for the help and insights... Glad I can help. :) s. isaac dealey954-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=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
