Welcome to WebmasterWorld Guest from 54.166.33.25

Forum Moderators: open

MySQL query pointer

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

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

10+ Year Member



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.

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

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

10+ Year Member



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)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



much easier to just scrub the data and be done with it. Remember: KISS
9:28 am on Sep 11, 2005 (gmt 0)

10+ Year Member



thanks :)
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month