[RESOLVED] Changing 'The Name' to 'Name, The' then order by alphabet

middleman666

Hey,

So I have a list, listed alphabetically:

Attic
Fish
The Bear

That I want to list like this:

Attic
Bear, The
Fish

Is there an easy way of doing this in a mysql query?
or would I have to put all results in an array first, changing the results with 'The' at the start, and then read the results out again alphabetically?

Thanks.

alimadzi

Doing that in a mysql query? Not that I know of, but if I'm wrong, I would love to see the solution.

Personally I would load the list into an array and format all the items as they're loaded (i.e. change "The Bear" to "Bear, The"). Then I would simply sort the array.

NogDog

Maybe...

SELECT 
  IF(`col` LIKE 'The %', CONCAT(SUBSTR(`col`, 5), ', The'), `col`) AS `list_item`
  FROM `table`
  ORDER BY `list_item`

alimadzi

Nicely done, NogDog. Now can you include 'A' and 'An'? Such as...

'A Bear' => 'Bear, A'
'An Apple' => 'Apple, An'

NogDog

SELECT
  CASE
    WHEN `col` LIKE 'The %' THEN CONCAT(SUBSTR(`col`, 5), ', The')
    WHEN `col` LIKE 'An %' THEN CONCAT(SUBSTR(`col`, 4), ', An')
    WHEN `col` LIKE 'A %' THEN CONCAT(SUBSTR(`col`, 3), ', A')
    ELSE `col` 
  END AS `list_item`
  FROM `table`
  ORDER BY `list_item`

alimadzi

Nicely done again. So does this fix your wagon, float your boat, or otherwise solve your problem middleman666? Be sure to mark the thread as resolved.

middleman666

cheers for all the replies, sounds like a great solution, but I can't seem to get it to work 🙁

I get this error:
Query failed (): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(FIELDNAME, 5), ', The'), FIELDNAME) AS list_item FROM table

Could it have something to do with my host only having mysql 4.0.27 installed?

alimadzi

Could you post the exact query you're running? It looks like it might be a bit different from NogDog's.

middleman666

$query = "SELECT IF(`Band` LIKE 'The %', CONCAT(SUBSTR(`Band`, 5), ', The'), `Band`) AS `list_item` FROM `TestTable` ORDER BY `list_item`";

//Get Results
$result = mysql_query($query) or die("Query failed: " . mysql_error());

//Display the rows
while ($row= mysql_fetch_array($result)) {
  echo $row['list_item'].'<br />';
}

bradgrafelman

Change SUBSTR() to SUBSTRING() in your query... should work then.

NogDog

Curious: The manual says SUBSTR() was added in MySQL 4.1.1 as an alias of SUBSTRING(), but I can only get the long name to work in my 5.0.27 version. 😕

middleman666

bradgrafelman wrote:
Change SUBSTR() to SUBSTRING() in your query... should work then.

Thanks, works perfectly! 🙂

RESOLVED -