Something like this would be great for sorting book lists, song titles, DVD and video libraries...
Cutter Candace Cottrell wrote: >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 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
