Hi.
Try this:
SELECT title FROM titles
ORDER BY
IF(SUBSTRING(title,1,4)="The ",SUBSTRING(title,5),
IF(SUBSTRING(title,1,2)="A ",SUBSTRING(title,3),
IF(SUBSTRING(title,1,3)="An ",SUBSTRING(title,4),
title)));
But in version 3.23.36 the next:
SELECT title FROM titles
ORDER BY
CASE
WHEN SUBSTRING(title,1,4)="The "
THEN SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)="A "
THEN SUBSTRING(title,3)
WHEN SUBSTRING(title,1,3)="An "
THEN SUBSTRING(title,4)
ELSE title
END;
works right.
Goodbye.
Dannis.
On Thu, Nov 01, 2001 at 01:09:52PM -0500, Ian M. Evans wrote:
> Back when I was working with MSSQL I needed to alphabetize movie titles in
> the proper library format where 'A' 'An" and 'The" are ignored.
>
> For MSSQL I was told to use:
> select * from titles order by case when title like 'The %' then substring
> (title, 5, 255) when title like 'A %' then substring (title, 3, 255) when
> title like 'An %' then substring (title, 4, 255) else Title end
>
> That worked like a charm, yet MySQL doesn't seem to accept that. Any
> solutions or advice?
>
> --
> Ian Evans
> Digital Hit Entertainment
> http://www.digitalhit.com
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
