homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

MySQL query pointer
How to reconstruct "the something" to "something, the" maintaining order

 12:55 pm on Sep 10, 2005 (gmt 0)

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

Acccccc, The
Aeeeeee, The

My table holds the above list as:

The Acccccc
The Aeeeeee
The Cdddddd
etc, etc.

Thanks in advance.



 4:32 pm on Sep 10, 2005 (gmt 0)

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.


 9:03 pm on Sep 10, 2005 (gmt 0)

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?


 9:12 pm on Sep 10, 2005 (gmt 0)

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


 9:28 am on Sep 11, 2005 (gmt 0)

thanks :)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved