How to sort by part of a string?

Question

SQL

There is a table with dates, some cells look like this:
07/18/2019
And some like this :
07/18/2019/08/18/2019/06/24/2019
cell.
For example, this cell on 07/18/2020 will be lower than this 07/18/2032/08/18/2029/06/24/2019 because the year is lower here.
Of course, you can shove everything into an array and do the selection and sorting there, but maybe there is some beautiful elegant way?

Answer 1

Normalize base, take out dates in the separate table and work already with it in a normal format, with join'ami. And not this whole trash.

Answer 2

In fact, these are not dates, but a string.

Answer 3

Look for an analogue of STRING_SPLIT for your DBMS . Use it to make a view with a date field, connect to the main table and group by it.

Answer 4

ORDER BY LEAST(
    IF(SUBSTR(`date`, 1, 10)='', '9999-01-01', STR_TO_DATE(SUBSTR(`date`, 1, 10), '%d.%m.%Y')),
    IF(SUBSTR(`date`, 12, 10)='', '9999-01-01', STR_TO_DATE(SUBSTR(`date`, 12, 10), '%d.%m.%Y')),
    IF(SUBSTR(`date`, 23, 10)='', '9999-01-01', STR_TO_DATE(SUBSTR(`date`, 23, 10), '%d.%m.%Y'))
)

The number of IFs must be equal to the maximum possible number of dates in a row.
This number can be found by running the following query:

SELECT 
    MAX(ROUND((LENGTH(`date`) - LENGTH(REPLACE(`date`, '/', ''))) / LENGTH('/')))+1 AS 'max' 
FROM 
    `dates`

... where `dates` is the name of the table and `date` is the name of the field