Home UncategorizedMySQL query to get table name with numerically highest number

MySQL query to get table name with numerically highest number

January 29, 2023•

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 18 times, 1 visits today)
Was this article helpful?
YesNo

Last modified: February 20, 2023

Close Search Window
↑