Sort records containing numbers at the end

Question

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 the 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 |
+----+------------+--------------+

Answer 1

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

You didn’t give many criteria about column format url.

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 drawing from position 11, right after ola-mundo-.

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

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

As reinforced by the colleague @jlHertel in comments, any sorting that is done this way does not take advantage of indexes in Mysql, which has no option to index either conditionally or using expressions, and that is why the suggestion of data reorganization.