homepage Welcome to WebmasterWorld Guest from 54.197.183.230
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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
davelms

10+ Year Member



 
Msg#: 21 posted 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

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

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



 
Msg#: 21 posted 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.

davelms

10+ Year Member



 
Msg#: 21 posted 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?

txbakers

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



 
Msg#: 21 posted 9:12 pm on Sep 10, 2005 (gmt 0)

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

davelms

10+ Year Member



 
Msg#: 21 posted 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