CODEWITHSUNDEEP
phpmysqlsql
Dee Doblar
Dee Doblar

Reputation: 9

This query is working but I want to know if I can have it shortened

I am trying to extract the last word from a string field... If it is a name of month, I want to convert it to month in Number. It is working but it's too long. Can we contain " Incorrect datetime value" or on date format error then display blank?

If then else not working well...

case 
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "January" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "February" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1))= "March" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "April" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "May" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "June" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "July" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "August" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "September" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "October" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "November" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
when (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) = "December" then (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
else null
end  as `Last_NuM_Month`

This sample is working but too long.

Upvotes: 0

Views: 61

Answers (2)

Caconde
Caconde

Reputation: 4493

Actually you can reduce your code to just the line below:

SELECT (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M'))) AS `Last_NuM_Month`;

MySQL's MONTH() will return NULL if you have an invalid month name like "februember" in your tbl_StringsList.items

Upvotes: 1

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could change to this:

CASE 
    WHEN (SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)) IN 
    (
        'January', 'February', 'March', 'April', 'May', 'June', 
        'July', 'August', 'September', 'October', 'November', 'December'
    )
    THEN (MONTH(str_to_date((SUBSTRING_INDEX(tbl_StringsList.items, " ", -1)),'%M')))
    ELSE NULL
END AS `Last_NuM_Month`

Tested in rextester

Upvotes: 0

Related Questions