Thanks to everyone who responded. I guess this is going to take a few minutes to do... Maybe I should write a cfc that does this??? For future posterity?? Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 1/8/2003 11:47:33 PM >>> In a perfect world, you use regular expressions, but you can probably get most of the work done using the replace() function in your dialect of SQL, or failing that, using the ColdFusion function. It's pretty ugly -- in MSSQL you could use a User-defined function or a view to make a table that doesn't look so nasty SELECT blahblah Replace(LEFT(title,3),"the","") as cleanTitle -snip- ORDER BY cleanTitle handles "the" you could nest them to do something like c or if you can support something like the T-SQL CASE (using sybase or mssql) you could use CASE and the LEFT or PATINDEX index function to chop things up -- something like (off the top of my head) SELECT cleantitle= CASE title WHEN LEFT(title,3) ='the' THEN SUBSTRING(title,4,len(title)) ELSE title END or something along those lines. A UDF would be great way to implement because then you can change the underlying mechanics (say from replace to CASE to regex) without recoding SELECT cleantitle(title) as cleanedTitle ORDER BY cleanedTitle Regards, John Paul Ashenfelter CTO/Transitionpoint [EMAIL PROTECTED] ----- Original Message ----- From: "Candace Cottrell" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 6:48 PM Subject: ORDER BY Clause and parsing > Good evening... > > I am trying to order a list of books by their Title. > However, many of the books begin witht the word "The" or "A" or "An". > > Is there an easy way to go through and say... > > <cfset actualTitle = query.Title minus A,An, or The> > > then the SQL... > > Select Title > >From Books > Order By #ActualTitle# > > Would I need to go through and do a left of the string Title > > <cfset leftVal = Left(query.Title, 3)> > <cfif leftVal IS "the"> > <cfset actualTitle = query.title - LeftVal> > > > Does this make sense??? I just dont think I'm "getting it". > > > TIA :) > > > Candace K. Cottrell, Web Developer > The Children's Medical Center > One Children's Plaza > Dayton, OH 45404 > 937-641-4293 > http://www.childrensdayton.org > > > [EMAIL PROTECTED] > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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