ORDER BY regex?

[Jeff]

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

[zac.carey]

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 ', '' );

[Rich Ryan]

"Jeff" <do*********@all.ukwrote in message
news:Jq*****************@newsread3.news.pas.earthl ink.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

[Rich Ryan]

<za*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.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

[Bill Karwin]

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.

[strawberry]

Rich Ryan wrote:

<za*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.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

[Jeff]

strawberry wrote:

Rich Ryan wrote:

>><za*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.google groups.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

>

[Jeff]

Rich Ryan wrote:

"Jeff" <do*********@all.ukwrote in message
news:Jq*****************@newsread3.news.pas.earthl ink.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.

<URL: http://dev.mysql.com/doc/refman/5.0/...functions.html />

I stumbled across this fascinating bit also, while searching:

<URL:
http://dev.mysql.com/doc/mysql/searc...s.html&lang=en
/>

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

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

[Bill Karwin]

Jeff wrote:

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

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/...t-boolean.html

That is part of MySQL standard.

Regards,
Bill K.