Christian Stromberger wrote: > Is there any way to build an index like this (to ignore A, An, The) for > faster title sorts? Any helpful advice for a newbie appreciated!
Not really. You could create a new column that has the sort name and put an index on that. But really, if you are going to search the title for key words you want a fulltext index which will ignore words less than four characters by default anyway. b. > > > -Chris > > > -----Original Message----- > > From: Denis Rudakov [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, November 13, 2001 2:03 AM > > To: [EMAIL PROTECTED] > > Subject: Re: Alphabetizing book titles > > > > > > 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 -- Bill Adams TriQuint Semiconductor --------------------------------------------------------------------- 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
