How to select max value of mixed string/int column?

When we have a table column that is a mix of string and number and want to select the max value of the number part, one way is to exclude the string part, convert the rest to int, and select the max value out of it.

For example, we have this table orders in MySQL:

id	order_id
1	TK12
2	TK19
3	TP99
4	AD100
5	TU65
6	MA33
7	PP98
8	DE28
9	TH54
10	VD66
11	GT74
12	PO1901

And we need to select the max value of order_id:

SELECT
  MAX(CAST(SUBSTRING(order_id, 3, length(order_id) - 2) AS UNSIGNED)) AS max_order
FROM
  orders;

The result is:

max_order
1901

Or select the min value of order_id:

SELECT
  MIN(CAST(SUBSTRING(order_id, 3, length(order_id) - 2) AS UNSIGNED)) AS min_order
FROM
  orders;

The result is:

min_order
12

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS