ORDER BY regex?

10/07/2006, 15h16

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

10/07/2006, 17h21

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

10/07/2006, 23h41

"Jeff" <dont_bug_me@all.uk> wrote in message
news:Jqssg.6631$cd3.1978@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

10/07/2006, 23h42

<zac.carey@gmail.com> wrote in message
news:1152544919.334216.228080@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

11/07/2006, 00h30

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.

11/07/2006, 01h31

Rich Ryan wrote:
> <zac.carey@gmail.com> wrote in message
> news:1152544919.334216.228080@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

11/07/2006, 01h48

strawberry wrote:
> Rich Ryan wrote:
>
>><zac.carey@gmail.com> wrote in message
>>news:1152544919.334216.228080@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

>

11/07/2006, 02h00

Rich Ryan wrote:

> "Jeff" <dont_bug_me@all.uk> wrote in message
> news:Jqssg.6631$cd3.1978@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?

11/07/2006, 18h53

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.

10/07/2006, 15h16	#1
Jeff Messages: n/a Hébergeur:	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

10/07/2006, 17h21	#2
zac.carey@gmail.com Messages: n/a Hébergeur:	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 ', '' );

10/07/2006, 23h41	#3
Rich Ryan Messages: n/a Hébergeur:	Re: ORDER BY regex? "Jeff" <dont_bug_me@all.uk> wrote in message news:Jqssg.6631$cd3.1978@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

10/07/2006, 23h42	#4
Rich Ryan Messages: n/a Hébergeur:	Re: ORDER BY regex? <zac.carey@gmail.com> wrote in message news:1152544919.334216.228080@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

11/07/2006, 00h30	#5
Bill Karwin Messages: n/a Hébergeur:	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.

11/07/2006, 01h31	#6
strawberry Messages: n/a Hébergeur:	Re: ORDER BY regex? Rich Ryan wrote: > <zac.carey@gmail.com> wrote in message > news:1152544919.334216.228080@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

11/07/2006, 01h48	#7
Jeff Messages: n/a Hébergeur:	Re: ORDER BY regex? strawberry wrote: > Rich Ryan wrote: > >><zac.carey@gmail.com> wrote in message >>news:1152544919.334216.228080@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 >

11/07/2006, 02h00	#8
Jeff Messages: n/a Hébergeur:	Re: ORDER BY regex? Rich Ryan wrote: > "Jeff" <dont_bug_me@all.uk> wrote in message > news:Jqssg.6631$cd3.1978@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?

11/07/2006, 18h53	#9
Bill Karwin Messages: n/a Hébergeur:	Re: ORDER BY regex? 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.

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email