How to search maximum id with constant value

Question

I have an id column in table with following data:

K1
K2
K3
....
876
765
887
K12
K13

I want to find the max id with constant 'K' I have used following syntax

Select max(id) from table where id like 'k%'

The end result is K9 How can I get the accurate result which is k13

Possible duplicate of [How to get only Digits from String in mysql?](https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql) — Justinas, Nov 28 '18 at 07:50
You could try using `SUBSTRING()` to remove the k before checking the max, but that might require using a sub-select. It would be helpful to know more about the table structure. — Snake14, Nov 28 '18 at 07:53

score 3 · Accepted Answer · edited Nov 28 '18 at 08:26

3

You where close, try:

Select CONCAT('K', MAX(CAST(REPLACE(id, 'K', '') AS UNSIGNED))) as maximum 
from table 
where id like 'K%'

edited Nov 28 '18 at 08:26

TalESid

answered Nov 28 '18 at 07:50

Added `CAST.. AS UNSIGNED` to your query; it is required to treat the modified string as integer value for comparison and determining MAX() value. – Madhur Bhaiya Nov 28 '18 at 07:54

score 1 · Answer 2 · edited Nov 28 '18 at 11:02

1

Try this.

Replace K with Empty String.
Convert String to Number.
Take MAX from the result.
Finally append K with MAX value.

SELECT CONCAT('K', MAX(CAST(REPLACE(id, 'K', '') AS UNSIGNED))) as max
FROM table
WHERE id LIKE 'K%';

edited Nov 28 '18 at 11:02

TalESid

answered Nov 28 '18 at 08:02

user3160387

That's the good one (Last point). Just a suggestion: Write your code in code blocks so the answer would be easily readable!!! – TalESid Nov 28 '18 at 08:17

2 Answers2