How Sorting Data With Underscore In Mysql

Question

How to sorting data when contained in the underscore ?

Explanation is on the picture,,

Thank you for help.

can the numbers be anny number? So `ABC_1_1` can also be `ABC_100_1000` ? And is the max of number of numbers always 2? — Raymond Nijland, Jul 12 '19 at 09:30
Please, put your example in your question, don't use external image — Vincent Decaux, Jul 12 '19 at 09:32
Hint: Research on this website how can split strings with nested SUBSTRING_INDEX functions and use CAST to convert it into a int.. Then you can simply use ORDER BY on those numbers .. Atleast if you are looking for a MySQL only method. — Raymond Nijland, Jul 12 '19 at 09:34
Technically it **is** sorting correctly - you're sorting on strings so `10` comes before `2`. You'd be better off altering your database table to add a `priority` column that's an integer type and sorting on that. — CD001, Jul 12 '19 at 09:39
*"You'd be better off altering your database table to add a priority column that's an integer type and sorting on that. "* @CD001 most likely but we don't know the topicstarters usercase.. topicstarter keep in mind adding a "sorting" column only really works when you only need to deal with one "group" which you need to sort... — Raymond Nijland, Jul 12 '19 at 09:47

score 2 · Answer 1 · answered Jul 12 '19 at 09:34

You want a natural sort.

<?php

$items =
[
'ABC_1_1',
'ABC_1_3',
'ABC_1_10',
'ABC_1_11',
'ABC_1_5',
'ABC_1_7'
];

sort($items, SORT_NATURAL);
var_dump($items);

Output:

array(6) {
    [0]=>
    string(7) "ABC_1_1"
    [1]=>
    string(7) "ABC_1_3"
    [2]=>
    string(7) "ABC_1_5"
    [3]=>
    string(7) "ABC_1_7"
    [4]=>
    string(8) "ABC_1_10"
    [5]=>
    string(8) "ABC_1_11"
  }

score 1 · Answer 2 · answered Jul 12 '19 at 09:33

1

You can try below -

select id_code
from tablename
order by 
substring_index(substring_index(id_code,'_',-2),'_',1),substring_index(id_code,'_',-1)

answered Jul 12 '19 at 09:33

Fahmi

37,315
5
22
31

Not working,, Any a simple query ? – Adit Prime Jul 15 '19 at 01:35

Vincent Decaux · Answer 3 · 2019-07-12T09:48:21.370

0

You can try in MYSQL to split your string, and order by parts :

SELECT SUBSTRING_INDEX(ID_DATA, '_', 1) as part1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(ID_DATA, '_', 2), '_', -1) as part2,
       SUBSTRING_INDEX(ID_DATA, '_', -1) as part3,
FROM table
ORDER BY part1, part2, part3

edited Jul 12 '19 at 09:48

answered Jul 12 '19 at 09:34

Vincent Decaux

9,857
6
56
84

you are using the nesting wrong... see [demo](https://www.db-fiddle.com/f/8rr4TNiZrM8Uuhrhzq2rb1/0) – Raymond Nijland Jul 12 '19 at 09:42
ah yes you're right – Vincent Decaux Jul 12 '19 at 09:48

How Sorting Data With Underscore In Mysql

3 Answers3