I'm trying to sort a MySQL table data based on a column values.

The column is named record_number and the values of column follow the format ent/4/2022, where ent is common in all entries and 4 is the record number and 2022 is the year the record was created.

How can I sort the records in a MySQL query such that an entry like ent/6/2021 shows up before ent/4/2022 when displaying the sorting results in ascending order?

The sorting result in descending order should look something like this.

This implies the record id = 1 is newer compared to record with id = 3 because it was created in 2021 and the latter in 2022.

CodePudding user response：

ORDER BY
  1*RIGHT(record_number, 4), /* extracts the year (assumes a 4-digit year) */
  1*SUBSTR( SUBSTRING_INDEX(record_number, '/', 2), 5 ) /* extracts record number */

1* converts the extracted strings into INT so they can be sorted numerically rather than alphabetically.

If years won't always be 4 digits, use:

ORDER BY
  1*RIGHT(record_number, LOCATE('/', REVERSE(record_number))-1),
  1*SUBSTR( SUBSTRING_INDEX(record_number, '/', 2), 5 )

MYSQL : Find the last occurrence of a character in a string

CodePudding user response：

One way:

select * 
from test
order by SUBSTRING_INDEX(SUBSTRING_INDEX(record_number,'/',-2),'/', 1) *1  asc,
         str_to_date(replace(record_number,'ent','00'),'%d/%m/%Y') desc;

https://dbfiddle.uk/nUOCNgy_

First you need to disable NO_ZERO_DATE then replace ent with 00 and use str_to_date to form a date column .

The line SUBSTRING_INDEX(SUBSTRING_INDEX(record_number,'/',-2),'/', 1) *1 asc will get only the charchters between / / and cast it to number