CODEWITHSUNDEEP
mysqlsqlsql-order-by
HF_
HF_

Reputation: 709

SQL Order by the number at the end of the string

I have a table with some columns and I want to order the selection by the column 'post_id'. The rows are like this: 'rgpost0', 'rgpost1','rcpost2', ...

How can I order the selection by the number at the end of the value of the column 'post_id' descending?

This code is not working: SELECT * FROM posts_info ORDER BY post_id+0 DESC

I don't want to change the column type to number. I just want to order by number at the end of the string.

Upvotes: 1

Views: 1368

Answers (3)

oycarlito
oycarlito

Reputation: 122

Try this

SELECT * FROM posts_info ORDER BY CAST(SUBSTR(post_id, 7) AS UNSIGNED) DESC;

Upvotes: 1

forpas
forpas

Reputation: 164064

You can use reverse() twice:

SELECT * FROM posts_info 
ORDER BY reverse(reverse(post_id) + 0) + 0 DESC

For this table:

create table posts_info(id int, post_id varchar(100));
insert into posts_info(id, post_id) values
(1, 'bob52'),
(2, 'alice634'),
(3, 'john12'),
(4, 'mark7'),
(5, 'mary001');

Results:

| id  | post_id  |
| --- | -------- |
| 2   | alice634 |
| 1   | bob52    |
| 3   | john12   |
| 4   | mark7    |
| 5   | mary001  |

See the demo.

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

If you are using MySQL 8+ then we can use REGEXP_SUBSTR here:

SELECT *
FROM posts_info
ORDER BY
    CAST(REGEXP_SUBSTR(post_id, '[0-9]+$') AS UNSIGNED) DESC,
    post_id DESC;

I added a second sorting level in case two post_id happen to end in the same number. In this case, I order descending by the entire post_id.

Upvotes: 4

Related Questions