Is "The" your only problem word? What about "A" or "An" and other words that
are usually ignored when sorting things like book titles?

I'd be surprised if there was any way to ignore specific words in an ORDER
BY; I've been writing SQL for 20+ years and have never seen anything like
that.

I think what you'll need to do is modify your data so that words like "The",
"A", "An", etc. are at the end of the column values. I believe libraries
traditionally cataloged books as illustrated in these examples:

The Raven ==> Raven, The
A Voyage to the Moon and a Trip Around It ==> Voyage to the Moon and a Trip
Around It, An

Another approach that *might* be easier - if you are using an appropriate
version of MySQL - is to create a view that modifies the data for you. You'd
need logic like this, which is pseudocode, NOT real SQL:

create view view01 as
select case
when word(subscriber,1) = 'The' then substring(2nd through final words)
concatenate 'The'
when word(subscriber,1) = 'An' then substring(2nd through final words)
concatenate 'An'
else subscriber
end,
other-columns
from ...
where ...

A view can't contain an ORDER BY so you'll have to put the order by in the
query that uses the view but that's easy:

select subscriber, ... from view01
order by subscriber

Rhino


----- Original Message ----- 
From: "Jack Lauman" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, June 26, 2005 2:06 PM
Subject: ORDER by Question


> I'm using a query similar to the following to get an ordered list.
>
> SELECT.... ORDER BY Subscriber ASC, Name ASC;
>
> How do I change this so that if the 'Name' field begins with "The " that
> the sort begins on the second word?  In other words I'd like to be able
> to return the word "The" but have it sort on whatever the second word is.
>
> Thanks,
>
> Jack
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
>
>



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to