>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] = '00000'>
<cfset zeroes[2] = '0000'>
<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\0000\2\3", "ALL")>
<cfset d6n = reReplace(d6n, "(\b|\D)(\d)(\b|\D)", "\1\00000\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=subscribe&forumid=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
