Dooh, Beat me to it! I'll post it anyway.
<cfset places=querynew('name')>
<cfset temp = QueryAddRow(places,4)>
<cfset temp = QuerySetCell(places, 'name', 'University of Alabama The', 1)>
<cfset temp = QuerySetCell(places, 'name', 'Florida State University', 2)>
<cfset temp = QuerySetCell(places, 'name', 'The BlahBlah Institute', 3)>
<cfset temp = QuerySetCell(places, 'name', 'The University of Chicago', 4)>
<cfset re = "^(University of|The University of|The) ">
<CFOUTPUT query="places">
#name# #left(rereplace(name,re,"","all"),1)#<br>
</CFOUTPUT>
Results:
University of Alabama The A
Florida State University F
The BlahBlah Institute B
The University of Chicago C
DRE
-----Original Message-----
From: Jerry Johnson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 3:36 PM
To: CF-Talk
Subject: Re: ahhh query
That was in neither English nor Geek.
I'll try again.
<cfparam name="letter" default="A">
<cfset ignoreWordList="university,of,the,college,institute">
<cfquery name="qSchools" datasource=""...>
select * from schools where I care
</cfquery>
<cfset newNameList=ValueList(qSchools.name,"|")>
<cfloop index="word" list="#ignoreWordList#">
<cfset newNameList=replace(newNameList,word,"","ALL")>
</cfloop>
<cfset newNameArray=listToArray(newNameList,"|")>
<cfset temp=queryAddColumn(qSchools,newName,newNameArray)>
<cfquery name="AlphaSchools" datasource="" dbtype="query">
select * from qSchools where left(newName,1)='#letter#' order by name
</cfquery>
Jerry Johnson
>>> [EMAIL PROTECTED] 05/23/02 05:27PM >>>
There are smarter/faster/bettter ways. But here is one.
Create a second field, and have that be the name for selection purposes.
Then sort by actual name.
When you insert into the db, put the data in two fields, RealName and
NewName.
If you don't want to precalc this data, or can't , you can do it live, but
it will cost.
I think a simple mask of words you DONT care about can solve this.
Build a list of words that do not matter for the sort.
<cfset IgnoreList="university,of,the,college,institute">
select em all.
create a list of the name column ValueList()
replace each word in the ignore list with "" (cfloop through ignore list,
replacing with newNameList with "")
list to array
add a column to the query, populating with array (queryAddColumn
query of query to get the list for "A", order by original name field
It ain't quick, but it might work.
>>> [EMAIL PROTECTED] 05/23/02 05:05PM >>>
blahhhh...
I have a template topped by the alphabet, all letters are links and they
need to display the correct info from the database on the page when clicked,
However the info that is being sorted will be names such as.....
University of Alabama (A)
Florida State University (F)
The BlahBlah Institute (B)
The University of Chicago (C)
so basically the word I need to check the first letter of could be ANYHWERE
in the name
ANY and ALL ideas are appreciated.......(short of finding a new line of
work) : )
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
