RE: Sort database records as a human not a computer.
Check out the SQL PATINDEX function. In combination with SUBSTRING and
REPLACE, you could retrieve a column that omits the leading words and then
ORDER BY that column.
-Original Message-
From: Ian Skinner [mailto:h...@ilsweb.com]
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:320549
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sort database records as a human not a computer.
I would agree with Brian and Claude on adding the column for sorting. It would be easy enough to adjust your INSERT / UPDATE calls to auto-create the sort version, like this: UPDATE ... SET title = , sortTitle = WHERE ID = ... ~| 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:320544 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sort database records as a human not a computer.
I'm with Claude on this one, your best bet it probably to generate another column such as sortTitle that you can then sort on. On Mon, Mar 16, 2009 at 11:39 AM, Claude Schneegans < schneeg...@internetique.com> wrote: > > >>but you can use string > manipulation functions in the ORDER BY clause to sort your data. > > That would prevent the query from using an index, and it may have some > impact on performance. > > An alternative would be to apply these functions once for all on the > titles, > store the result in an extra column and ORDER BY this column. > > ~| 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:320542 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sort database records as a human not a computer.
>>but you can use string manipulation functions in the ORDER BY clause to sort your data. That would prevent the query from using an index, and it may have some impact on performance. An alternative would be to apply these functions once for all on the titles, store the result in an extra column and ORDER BY this column. ~| 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:320537 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Sort database records as a human not a computer.
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 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:h...@ilsweb.com]
> 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
RE: Sort database records as a human not a computer.
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:h...@ilsweb.com]
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:320533
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Sort database records as a human not a computer.
If you'd like to keep things simple, create a new column and enter the title
as you'd like it to be ordered by. Would love to hear if someone has an idea
about this also.
Adrian
> -Original Message-
> From: Ian Skinner [mailto:h...@ilsweb.com]
> Sent: 16 March 2009 14:27
> 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:320532
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
