Sort records containing numbers at the end

Question

Sort records containing numbers at the end

Navigation

#1 by (5 votes)

1

I have a table with the following records:

+----+------------+--------------+
| id |   title    |     url      |
+----+------------+--------------+
|  1 | Olá Mundo! | ola-mundo-1  |
|  5 | Olá Mundo! | ola-mundo-3  |
|  6 | Olá Mundo! | ola-mundo-10 |
|  7 | Olá Mundo! | ola-mundo-2  |
|  8 | Olá Mundo! | ola-mundo-15 |
+----+------------+--------------+

I need to sort by column url considering the number that exists at the end.

+----+------------+--------------+
| id |   title    |     url      |
+----+------------+--------------+
|  1 | Olá Mundo! | ola-mundo-1  |
|  7 | Olá Mundo! | ola-mundo-2  |
|  5 | Olá Mundo! | ola-mundo-3  |
|  6 | Olá Mundo! | ola-mundo-10 |
|  8 | Olá Mundo! | ola-mundo-15 |
+----+------------+--------------+

mysql sql order

asked by anonymous 14.02.2017 / 07:37

1 answer

How to check if the form was sent empty Remove HTML tags from an Input with PHP

score 5 · Accepted Answer

It would probably be the case to rethink the DB organization, perhaps separating this information into two parts (storing only the number, if the rest repeats) or storing in separate columns.

You did not give too many criteria about the format of the url column.

If the size is fixed, this is enough to extract only the numerical part:

 SELECT id, title, url FROM tabela ORDER BY SUBSTRING(url, 11);

The function SUBSTRING extracts a "piece" from another string . In the above example, we are extracting from position 11, just after ola-mundo- .

Now, I imagine you want to order numerically, so that numbers like 10, starting at 1, do not stay before 2, 3, 4 etc. In this case, just force a numerical comparison, not a string:

 SELECT id, title, url FROM tabela ORDER BY 0 + SUBSTRING(url, 11);

As reinforced by colleague @jlHertel in comments, any ordering that is done this way does not take advantage of indexes in MySQL, which it does not have the option of indexing neither conditionally, nor using expressions, and is therefore the suggestion of reorganization of the data.