sql server - how to get a month from list of strings in format of YYYYMM in sql?

Question

I have a list of YYYYMM formatted strings delimited by comma

('202001', '202003', '202012')

I would like to convert this into each row in a table.

monthNumber
-----------
Month1
Month3
Month12

I know how to do for one string and not for a list.

select CONCAT('Month', (datepart(month,('200602')+'01'))) as monthNumber.

Updated question:

Is there any way to convert the list of strings into each row in a table? As per @Gordon Linoff comment, if it's not supported by SQL SERVER except for WHERE IN clause. Any kind of magic in SQL Server to convert the WHERE IN clause values to each row in a table? Any help would be appreciated.

question from:https://stackoverflow.com/questions/66067274/how-to-get-a-month-from-list-of-strings-in-format-of-yyyymm-in-sql

Answer 1

Step one is to get the list in a format SQL Server recognizes. As a single string the example does not properly escape the single-quotes. If the "list" were a string with escaped single-quotes then you could do something like this

declare @list_of_strings            varchar(8000)='(''202001'', ''202003'', ''202012'')';

select concat('Month', cast(substring(ltrim(rtrim(sp.value)), 6, 2) as int)) monthNumber
from string_split(substring(@list_of_strings, 2, len(@list_of_strings)-2), ',') sp;

monthNumber
Month1
Month3
Month12