CODEWITHSUNDEEP
mysqlsqlstored-procedures
Edward Sheriff Curtis
Edward Sheriff Curtis

Reputation: 497

Find table name on the database using stored procedure on MySql 8.0.17

This is a list name of tables stored a database MySql version 8.0.17

+------------------+
| listTable        |
+------------------+
| Table_A2_11_2021 |
| Table_L7_12_2021 |
| Table_C3_1_2021  |
| Table_D8_10_2021 |
| Table_T0_11_2021 |
| Table_E9_3_2021  |
| Table_L4_2_2021  |
| Table_O1_12_2021 |
| Table_P2_5_2021  |
| Table_Q2_10_2021 |
| Table_A3_12_2021 |
| Table_S5_9_2021  |
| Table_T8_11_2021 |
| Table_Q6_1_2021  |
+------------------+

The table name storage policy is

I need find on the database MySql all the table for first Western alphabet letter without Random_number for this return

+-----------------+
| listTable       |
+-----------------+
| Table_A_11_2021 |
| Table_L_12_2021 |
| Table_C_1_2021  |
| Table_D_10_2021 |
| Table_T_11_2021 |
| Table_E_3_2021  |
| Table_L_2_2021  |
| Table_O_12_2021 |
| Table_P_5_2021  |
| Table_Q_10_2021 |
| Table_A_12_2021 |
| Table_S_9_2021  |
| Table_T_11_2021 |
| Table_Q_1_2021  |
+-----------------+

I have idea to use this Stored Procedure below but I just can't extract first Western alphabet letter without Random number.

CREATE DEFINER=`root`@`%` PROCEDURE `SP_SIX_MONTHS`()
BEGIN

DECLARE tyear INT(4);
DECLARE tmonth INT(2);

SET tyear = YEAR(CURDATE());
SET tmonth = MONTH(DATE_SUB(CURDATE(),INTERVAL 6 MONTH));

SET @s = CONCAT('SELECT 
                 FROM information_schema.TABLES 
                 WHERE table_name LIKE ''table#_',???,'%#_',tmonth,'#_',tyear,''' ESCAPE ''#'';');                       
                                 
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

UPDATE

return of suggestion

+------------+
| t          |
+------------+
| Table_A_13 |
| Table_C_12 |
| Table_D_1  |
| Table_E_5  |
| Table_L_12 |
| Table_O_8  |
| Table_P_12 |
| Table_Q_6  |
| Table_S_14 |
| Table_T_4  |
+------------+
10 rows in set (0.12 sec)

Upvotes: 0

Views: 195

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Hmmm . . . I think this actually does what you want:

select min(listtable)
from t
group by substr(listtable, 7, 1);

This interprets "first" as "first alphabetically".

If you don't want the digit, you can remove that:

select min(insert(listtable, 8, 1, ''))
from t
group by substr(listtable, 7, 1);

Here is a db<>fiddle.

Upvotes: 1

Related Questions