To get Month Name in place of Table Name-CodePudding

I have following MONTH wise tables...

vbSuccessfulCDR_597
vbSuccessfulCDR_598
vbSuccessfulCDR_599
vbSuccessfulCDR_600
vbSuccessfulCDR_601 and so on..

Following query gives me the table list.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'vbSuccessfulCDR_%';

I want to see month name as output in another column which corresponds to the TABLE_NAME since I know vbSuccessfulCDR_597 represents January'2021 vbSuccessfulCDR_598 represents February'2021 and so on... This will help me to search date range from different tables.

Is there any way to get month name in place of table name?

CodePudding user response：

You may use the TIMESTAMPADD() function here:

SELECT TABLE_NAME,
       TIMESTAMPADD(MONTH,
                    CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) - 597,
                    '2021-01-01') AS month
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'vbSuccessfulCDR_%';