CODEWITHSUNDEEP
mysqlsqlstringsorting
brianlasta
brianlasta

Reputation: 314

MySQL - sort by certain last string character

I'm trying to sort by a certain character on a string, for example,

before:

+----+---------+
| id |  name   |
+----+---------+
| 1  | red     |
| 2  | red-a   |
| 3  | red-xy  |
| 4  | blue    |
| 5  | blue-a  |
| 6  | blue-xy |
+----+---------+

after:

+----+---------+
| id |  name   |
+----+---------+
| 4  | blue    |
| 1  | red     |
| 5  | blue-a  |
| 2  | red-a   |
| 6  | blue-xy |
| 3  | red-xy  |
+----+---------+

are there any ways to categorize based on -a or -xy using ORDER BY

Thank you in advance.

Upvotes: 2

Views: 92

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

These queries are more readable and this is probably the easiest way to sort on a suffix

SELECT 
 *
 , IF (LOCATE('-', name) = 0
     , 0
     , LENGTH(SUBSTRING_INDEX(name, '-', -1))   
   )
     suffix_length

FROM 
 Table1

ORDER BY 
 suffix_length
;



SELECT 
 *

FROM 
 Table1

ORDER BY 
  IF (LOCATE('-', name) = 0
     , 0
     , LENGTH(SUBSTRING_INDEX(name, '-', -1))   
   )
;

See demo http://sqlfiddle.com/#!9/92b63/41

Upvotes: 1

mcr
mcr

Reputation: 782

This will do what you're looking for. Wouldn't like to promise great performance if you had a lot of rows though:

select id, name from 
(
  select id, 
         name, 
         if (substring_index(name,'-', -1) = name, '', substring_index(name,'-', -1)) as grouping
  from Table1
  order by grouping, name
) as subTable

SQLFiddle here

[EDIT] Actually, that can be simplified to a single select with :

select id, 
       name
from Table1
order by  if (substring_index(name,'-', -1) = name, '', substring_index(name,'-', -1)), name

Upvotes: 1

Sebas
Sebas

Reputation: 21532

SELECT 
  CASE 
    WHEN RIGHT(`name`,LENGTH(`name`)-INSTR(`name`,'-')) = `name` THEN ''
    ELSE RIGHT(`name`,LENGTH(`name`)-INSTR(`name`,'-')) 
  END AS `suffix`,
  `name`
FROM 
  `table1`
ORDER BY
  `suffix`, `name`

If no suffix is found, it will put the record in the first row set. Caveat: the first dash is used to separate the word from the prefix.

Upvotes: 1

Related Questions