ORDER BY regex?

I have a list like this:

Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house

I'd like to reorder that ignoring the leading "The".

How do I do that?

Jeff

Re: ORDER BY regex?

Jeff wrote:
> I have a list like this:
>
> Reckless Engineer
> St Bonaventures
> The Blue Mountain
> The Croft
> The Cube Club
> The Folk house
>
> I'd like to reorder that ignoring the leading "The".
>
> How do I do that?
>
> Jeff

This should work

SELECT *
FROM table1
ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );

Re: ORDER BY regex?

"Jeff" wrote in message
news:Jqssg.6631$cd3.1978@newsread3.news.pas.earthlink.net...
> I have a list like this:
>
> Reckless Engineer
> St Bonaventures
> The Blue Mountain
> The Croft
> The Cube Club
> The Folk house
>
> I'd like to reorder that ignoring the leading "The".
>
> How do I do that?
>
> Jeff

Here is something I posted a while back. It will work for you if you
understand it.

CREATE TABLE Titles(
Title varchar(100));

INSERT INTO TITLES VALUES('The World According to
Garp'),('Casablanca'),('The Day After Tomorrow');


SELECT
CASE
WHEN SUBSTRING(Title,1,4) = 'The '
THEN CONCAT(SUBSTRING(Title,5,LENGTH(Title) - 4),',The')
ELSE Title
END as T
FROM
Titles
ORDER BY T ASC

Re: ORDER BY regex?

wrote in message
news:1152544919.334216.228080@h48g2000cwc.googlegroups.com.. .
> Jeff wrote:
> > I have a list like this:
> >
> > Reckless Engineer
> > St Bonaventures
> > The Blue Mountain
> > The Croft
> > The Cube Club
> > The Folk house
> >
> > I'd like to reorder that ignoring the leading "The".
> >
> > How do I do that?
> >
> > Jeff
>
> This should work
>
> SELECT *
> FROM table1
> ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
>

How can this possibly work?

Rich

Re: ORDER BY regex?

Rich Ryan wrote:
>> ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
>
> How can this possibly work?

The first problem I see with this is that it replaces _all_ occurrences
of "the ", not just occurrences of the whole word "the" at the beginning
of the string.

Regards,
Bill K.

Re: ORDER BY regex?

Rich Ryan wrote:
> wrote in message
> news:1152544919.334216.228080@h48g2000cwc.googlegroups.com.. .
> > Jeff wrote:
> > > I have a list like this:
> > >
> > > Reckless Engineer
> > > St Bonaventures
> > > The Blue Mountain
> > > The Croft
> > > The Cube Club
> > > The Folk house
> > >
> > > I'd like to reorder that ignoring the leading "The".
> > >
> > > How do I do that?
> > >
> > > Jeff
> >
> > This should work
> >
> > SELECT *
> > FROM table1
> > ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
> >
>
> How can this possibly work?
>
> Rich

eh? but it DOES work !?! - well, up to a point

Re: ORDER BY regex?

strawberry wrote:
> Rich Ryan wrote:
>
>> wrote in message
>>news:1152544919.334216.228080@h48g2000cwc.googlegroups.com ...
>>
>>>Jeff wrote:
>>>
>>>>I have a list like this:
>>>>
>>>>Reckless Engineer
>>>>St Bonaventures
>>>>The Blue Mountain
>>>>The Croft
>>>>The Cube Club
>>>>The Folk house
>>>>
>>>> I'd like to reorder that ignoring the leading "The".
>>>>
>>>> How do I do that?
>>>>
>>>> Jeff
>>>
>>>This should work
>>>
>>>SELECT *
>>>FROM table1
>>>ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
>>>
>>
>>How can this possibly work?
>>
>>Rich
>
>
> eh? but it DOES work !?! - well, up to a point

Satisfied my needs, and if there was a "the" elsewhere, the ordering
couldn't be far off.

Is it possible to either sub in my own function or use something more
regex like? I can think of lots of times when I'd want to lead the
returns with a certain item.

Jeff


>

Re: ORDER BY regex?

Rich Ryan wrote:

> "Jeff" wrote in message
> news:Jqssg.6631$cd3.1978@newsread3.news.pas.earthlink.net...
>
>> I have a list like this:
>>
>>Reckless Engineer
>>St Bonaventures
>>The Blue Mountain
>>The Croft
>>The Cube Club
>>The Folk house
>>
>> I'd like to reorder that ignoring the leading "The".
>>
>> How do I do that?
>>
>> Jeff
>
>
> Here is something I posted a while back. It will work for you if you
> understand it.
>
> CREATE TABLE Titles(
> Title varchar(100));
>
> INSERT INTO TITLES VALUES('The World According to
> Garp'),('Casablanca'),('The Day After Tomorrow');
>
>
> SELECT
> CASE
> WHEN SUBSTRING(Title,1,4) = 'The '
> THEN CONCAT(SUBSTRING(Title,5,LENGTH(Title) - 4),',The')
> ELSE Title
> END as T
> FROM
> Titles
> ORDER BY T ASC
>
>


Thank, I had no idea that CASE existed.



I stumbled across this fascinating bit also, while searching:

http://dev.mysql.com/doc/mysql/search.php?version=5.0&q=when &from=%2Fdoc%2Frefman%2F5.0%2Fen%2Fcontrol-flow-functions.ht ml&lang=en
/>


+apple +(>turnover Find rows that contain the words ``apple'' and ``turnover'', or
``apple'' and ``strudel'' (in any order), but rank ``apple turnover''
higher than ``apple strudel''.


Is that a part of MySQL standard SQL commands, or is that for FULL TEXT
searches only?

Re: ORDER BY regex?

Jeff wrote:
>
> +apple +(>turnover > Find rows that contain the words ``apple'' and ``turnover'', or
> ``apple'' and ``strudel'' (in any order), but rank ``apple turnover''
> higher than ``apple strudel''.
>
>
> Is that a part of MySQL standard SQL commands, or is that for FULL TEXT
> searches only?

It's part of FULL TEXT searching.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

That is part of MySQL standard.

Regards,
Bill K.