MySQL query pointer

How to reconstruct "the something" to "something, the" maintaining order

[davelms]

Hi, a quick pointer would be most welcome. I have a table containing names (band names to be specific). Basically I want to find all names starting with, for example, A.

I just decided that "The Aaaaa" should be in the A section not the T section, recontrustced as "Aaaaa, The".

I wanted to know if anyone had any pointers as to how I can reformat the "The" names, and order so that all rows are returned in name order post-reformatting. eg

Aaaaaaa
Abbbbbb
Acccccc, The
Adddddd
Aeeeeee, The
Affffff

My table holds the above list as:

Aaaaaaa
Abbbbbb
Adddddd
Affffff
Bbbbbbb
Ccccccc
Ddddddd
The Acccccc
The Aeeeeee
The Cdddddd
etc, etc.

Thanks in advance.

[txbakers]

What you could do is to read through the record set, and on each row check for the word "the", parse it out and do the comma switcheroo.

But that wouldn't help you in an order by, since the "T" is that way in the database.

You could also do it when stuff gets added to the database - check to see if it starts with The, look for the space, and do the switcheroo.

A third option would be to write some type of formula in the ORDER BY that would look for the word "The" and if so, skip the first 4 characters. Complex, but doable.

Personally, I would just scrub the data and make sure it never happened again.

[davelms]

Cool, thankyou. I was thinking as much, and yeah, it was really the ordering that was my confusion (I was quite comfortable with the reformat). Before I amended what was held in my tables, I just wanted to verify there was nothing I could do via MySQL at query time. Thanks again.

edit - when you say "complex, but doable" is there any clue as to what I should look at? I'm actually ok once shoved in the right direction, so I don't need solutions just a pointer ... so if there's some hints someone can give for what to do within the ORDER BY please do set me going, as it were. Or is it really a recommendation to restructure the data on file than for me to spend effort to review my SQL?

[txbakers]

much easier to just scrub the data and be done with it. Remember: KISS

[davelms]

thanks :)