CODEWITHSUNDEEP
mysql
Simone Rossaini
Simone Rossaini

Reputation: 8162

Turn a written month into a number mysql

I have this structure in mysql:

+----+---------------+------+
| ID | DATE          | WHAT |
+----+---------------+------+
| 1  | GENNAIO 2020  | 1    |
+----+---------------+------+
| 2  | FEBBRAIO 2021 | 2    |
+----+---------------+------+
| 3  | GENNAIO 2020  | 1    |
+----+---------------+------+

I should be able to sort it by date, unfortunately being written in letters I don't know how to proceed. I emphasize that I cannot use the id because the entry could be different from the written date.

PS. The months are written in Italian (January, February)

The complete answer from suggest of @Tim Biegeleisen

SELECT `ID`,`DATE`,CASE 
              WHEN `DATE` LIKE '%GENNAIO%' THEN   CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','01','-10')
              WHEN `DATE` LIKE '%FEBBRAIO%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','02','-10')
              WHEN `DATE` LIKE '%MARZO%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','03','-10')
              WHEN `DATE` LIKE '%APRILE%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','04','-10')
              WHEN `DATE` LIKE '%MAGGIO%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','05','-10')
              WHEN `DATE` LIKE '%GIUGNO%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','06','-10')
              WHEN `DATE` LIKE '%LUGLIO%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','07','-10')
              WHEN `DATE` LIKE '%AGOSTO%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','08','-10')
              WHEN `DATE` LIKE '%SETTEMBRE%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','09','-10')
              WHEN `DATE` LIKE '%OTTOBRE%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','10','-10')
              WHEN `DATE` LIKE '%NOVEMBRE%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','11','-10')
              WHEN `DATE` LIKE '%DICEMBRE%' THEN CONCAT(SUBSTRING_INDEX(`DATE`, ' ', -1),'-','12','-10')
              END AS DATAA
FROM `mytable` order by DATAA DESC

I added %MONTHS% to find the month and I added the quotes for the month so that November and December also work. Obviously as pointed out in the comments below it is always better to use a date in number, in fact I will use this query to transform the DB

Upvotes: 0

Views: 68

Answers (2)

forpas
forpas

Reputation: 164139

Simpler than a CASE expression is FIND_IN_SET():

SELECT id, 
       FIND_IN_SET(
         SUBSTRING_INDEX(date, ' ', 1),
         'GENNAIO,FEBBRAIO,MARZO,APRILE,MAGGIO,GIUGNO,LUGLIO,AGOSTO,SETTEMBRE,OTTOBRE,NOVEMBRE,DICEMBRE'
       ) month,
       SUBSTRING_INDEX(date, ' ', -1) year
FROM mytable

See the demo.
Results:

> id | month | year
> -: | ----: | :---
>  1 |     1 | 2020
>  2 |     2 | 2020
>  3 |     1 | 2020

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

You could use a CASE expression here:

SELECT
    ID,
    DATE,
    CASE WHEN DATE LIKE 'GENNAIO' THEN 1
         WHEN DATE LIKE 'FEBBRAIO' THEN 2
         ... END AS MONTH
FROM yourTable;

A better way to handle this would be to use STR_TO_DATE, and convert the text dates to bona fide dates, then extract the numeric month. This assumes that STR_TO_DATE on your MySQL would have locale support for Italian. But an even better approach would be to stop storing your dates as text. Just use a bona fide date column and avoid these problems.

Upvotes: 3

Related Questions