I have a table storing artist_names in text format linked to other
tables by a numeric auto_increment field. The artist_name field is a
varchar field.

The artist_name field holds the artist name sometimes including 'the' or
'a' at the beginning of the field. Now I would like to sort them
using the artist name, ignoring the 'a' and 'the' at the start is there
some clever way to do this?

I would like for instance that 'The Smiths' would be listed under S
instead of under T when I issue a simple select key. Do I have to make
some clever join?

Thanks in advance,

Jonathan

On Nov 25, 7:28 pm, Jonathan <jonat...@heelal.nl> wrote:
> I have a table storing artist_names in text format linked to other
> tables by a numeric auto_increment field. The artist_name field is a
> varchar field.
>
> The artist_name field holds the artist name sometimes including 'the' or
> 'a' at the beginning of the field. Now I would like to sort them
> using the artist name, ignoring the 'a' and 'the' at the start is there
> some clever way to do this?
>
> I would like for instance that 'The Smiths' would be listed under S
> instead of under T when I issue a simple select key. Do I have to make
> some clever join?
>
> Thanks in advance,
>
> Jonathan

Here's one way:

SELECT * FROM my_albums ORDER BY REPLACE(REPLACE(`artist`,'The
',''),'A ','');

Jonathan wrote:
> I have a table storing artist_names in text format linked to other
> tables by a numeric auto_increment field. The artist_name field is a
> varchar field.
>
> The artist_name field holds the artist name sometimes including 'the'
> or 'a' at the beginning of the field. Now I would like to sort
> them using the artist name, ignoring the 'a' and 'the' at the start is
> there some clever way to do this?
>
> I would like for instance that 'The Smiths' would be listed under S
> instead of under T when I issue a simple select key. Do I have to make
> some clever join?
>
> Thanks in advance,
>
> Jonathan

Here is one way

SELECT
artist_name
FROM the_table
ORDER BY
CASE
WHEN SUBSTRING_INDEX(artist_name, ' ',1) = 'The' THEN
SUBSTRING(artist_name,5)
WHEN SUBSTRING_INDEX(artist_name, ' ',1) = 'a' THEN
SUBSTRING(artist_name,3)
ELSE artist_name
END

This assumes that the "the" or "a" is followed by only 1 space.

strawberry wrote:
> On Nov 25, 7:28 pm, Jonathan <jonat...@heelal.nl> wrote:
>> I have a table storing artist_names in text format linked to other
>> tables by a numeric auto_increment field. The artist_name field is a
>> varchar field.
>>
>> The artist_name field holds the artist name sometimes including
>> 'the' or 'a' at the beginning of the field. Now I would like
>> to sort them
>> using the artist name, ignoring the 'a' and 'the' at the start is
>> there some clever way to do this?
>>
>> I would like for instance that 'The Smiths' would be listed under S
>> instead of under T when I issue a simple select key. Do I have to
>> make some clever join?
>>
>> Thanks in advance,
>>
>> Jonathan
>
> Here's one way:
>
> SELECT * FROM my_albums ORDER BY REPLACE(REPLACE(`artist`,'The
> ',''),'A ','');

Wouldn't that make
a band called "Spank The Monkey" become "Spank Monkey"?

On Nov 25, 10:38 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> strawberry wrote:
> > On Nov 25, 7:28 pm, Jonathan <jonat...@heelal.nl> wrote:
> >> I have a table storing artist_names in text format linked to other
> >> tables by a numeric auto_increment field. The artist_name field is a
> >> varchar field.
>
> >> The artist_name field holds the artist name sometimes including
> >> 'the' or 'a' at the beginning of the field. Now I would like
> >> to sort them
> >> using the artist name, ignoring the 'a' and 'the' at the start is
> >> there some clever way to do this?
>
> >> I would like for instance that 'The Smiths' would be listed under S
> >> instead of under T when I issue a simple select key. Do I have to
> >> make some clever join?
>
> >> Thanks in advance,
>
> >> Jonathan
>
> > Here's one way:
>
> > SELECT * FROM my_albums ORDER BY REPLACE(REPLACE(`artist`,'The
> > ',''),'A ','');
>
> Wouldn't that make
> a band called "Spank The Monkey" become "Spank Monkey"?

It only effects order so I don't think it would make much difference.

On Nov 25, 10:58 pm, strawberry <zac.ca...@gmail.com> wrote:
> On Nov 25, 10:38 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
> > strawberry wrote:
> > > On Nov 25, 7:28 pm, Jonathan <jonat...@heelal.nl> wrote:
> > >> I have a table storing artist_names in text format linked to other
> > >> tables by a numeric auto_increment field. The artist_name field is a
> > >> varchar field.
>
> > >> The artist_name field holds the artist name sometimes including
> > >> 'the' or 'a' at the beginning of the field. Now I would like
> > >> to sort them
> > >> using the artist name, ignoring the 'a' and 'the' at the start is
> > >> there some clever way to do this?
>
> > >> I would like for instance that 'The Smiths' would be listed under S
> > >> instead of under T when I issue a simple select key. Do I have to
> > >> make some clever join?
>
> > >> Thanks in advance,
>
> > >> Jonathan
>
> > > Here's one way:
>
> > > SELECT * FROM my_albums ORDER BY REPLACE(REPLACE(`artist`,'The
> > > ',''),'A ','');
>
> > Wouldn't that make
> > a band called "Spank The Monkey" become "Spank Monkey"?
>
> It only effects order so I don't think it would make much difference.

But then again, your post is a more elegant and correct solution. In
fact, it reminds me of another...

http://www.arraystudio.com/as-worksh...-the-a-an.html

strawberry wrote:
> But then again, your post is a more elegant and correct solution. In
> fact, it reminds me of another...
>
> http://www.arraystudio.com/as-worksh...-the-a-an.html

Hi Strawberry, that is sweet ;-)

Thanks for the link.

Jonathan