You can use the following MySQL query to get the table name with the numerically highest number:
sql
SET @max = 0;
SET @table_name = '';
SELECT table_name
FROM information_schema.tables
WHERE table_name REGEXP '^[a-zA-Z]+[0-9]+$'
AND CAST(SUBSTRING(table_name, LENGTH(table_name) - LENGTH(table_name) + 2) AS UNSIGNED) >= @max
ORDER BY CAST(SUBSTRING(table_name, LENGTH(table_name) - LENGTH(table_name) + 2) AS UNSIGNED) DESC
LIMIT 1;
This query uses a regular expression to match table names that contain letters followed by numbers. The SUBSTRING function is used to extract the numerical part of the table name and cast it to an unsigned integer using CAST. The query then selects the table name with the highest numerical value, using the ORDER BY clause and the LIMIT 1 clause to only return one result.
(Visited 5 times, 1 visits today)
Was this article helpful?
YesNo
Last modified: February 20, 2023
