Ian, you could do something like this... select * from footest order by (case when title like '"%' then substr(title, 2, length(title)) when title like 'a %' then substr(title, 3, length(title)) when title like 'an %' then substr(title, 4, length(title)) when title like 'the %' then substr(title, 5, length(title)) else title end)
But if you get a bunch of records I'd suggest an "orderTitle" column like suggested prior On Mon, Mar 16, 2009 at 9:39 AM, Dawson, Michael <[email protected]> wrote: > > I'm not sure if I follow you, completely, but you can use string > manipulation functions in the ORDER BY clause to sort your data. For > example, you can use the replace function to remove quotes, for sorting. > > Thanks, > Mike > > -----Original Message----- > From: Ian Skinner [mailto:[email protected]] > Sent: Monday, March 16, 2009 9:27 AM > To: cf-talk > Subject: Sort database records as a human not a computer. > > > With SQLServer (2000 I believe) if that matters, which it may well. > > I have a data column that contains titles. I desire the titles to be > sorted alphabetically as a human would not a computer. The records I'm > currently concerned with start with a quote (") and need to start with a > quote. Ideally I would like the records sorted with these records > placed in order by the first letter inside the quotes like a human > librarian would not to the top of the list as a computer does it. Since > I'm wishing for this, it would be really cool if the sort also ignored > the other human librarian no goes 'a', 'an' and 'the' that are supposed > to be ignored at the beginning of titles when sorting. > > Luckily this is a very minor requirement and can be ignored if there is > not is not a simple solution out there, but I also can't believe I am > the first programmer that needed to do this with database data, so maybe > the functionality already exists. > > TIA > Ian > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320536 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
