Re: ORDER BY Clause and parsing
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... >> >> >> >> then the SQL... >> >>Select Title >>>From Books >>Order By #ActualTitle# >> >>Would I need to go through and do a left of the string Title >> >> >> >> >> >> >>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
Re: ORDER BY Clause and parsing
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... > > > > then the SQL... > > Select Title > >From Books > Order By #ActualTitle# > > Would I need to go through and do a left of the string Title > > > > > > > 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
Re: ORDER BY Clause and parsing
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... > > > > then the SQL... > > Select Title > >From Books > Order By #ActualTitle# > > Would I need to go through and do a left of the string Title > > > > > > > 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: ORDER BY Clause and parsing
I think the best solution would be to have the title in two columns: Prefix and TheRest (or some better name). Then create a view with the readable title: prefix + ' ' + TheRest AS FullTitle and the sortable title: TheRest + ', ' + prefix AS SortTitle. > -Original Message- > From: Candace Cottrell [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 9 January 2003 12:49 p.m. > To: CF-Talk > 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... > > > > then the SQL... > > Select Title > >From Books > Order By #ActualTitle# > > Would I need to go through and do a left of the string Title > > > > > > > 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
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... then the SQL... Select Title >From Books Order By #ActualTitle# Would I need to go through and do a left of the string Title 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
