469,623 Members | 1,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

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
Jul 10 '06 #1
8 8371
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 ', '' );

Jul 10 '06 #2

"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
Jul 10 '06 #3

<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
Jul 10 '06 #4
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.
Jul 10 '06 #5

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

Jul 10 '06 #6
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

>
Jul 10 '06 #7
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?

Jul 10 '06 #8
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.
Jul 11 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Tim Conner | last post: by
3 posts views Thread by Ryan Taylor | last post: by
6 posts views Thread by Extremest | last post: by
7 posts views Thread by Extremest | last post: by
9 posts views Thread by Greg Collins [Microsoft MVP] | last post: by
3 posts views Thread by aspineux | last post: by
15 posts views Thread by morleyc | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.