Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
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
My table holds the above list as:
Thanks in advance.
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.
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?