How to make a number from a string, and then again a string?

Question

MySQL

I have a table of accelerations: id (int), speed (varchar)
The data is filled in this way:
1 | 4x2
| 10x3
| 15x4
| 12x
I need to find the maximum value. If I compare varchar, then 4x is the maximum...
How can I make a SELECT that translates to numbers first, selects the maximum, and then translates to a string and appends 'x' at the end? I tried to cast in cast, but I get an error:
CAST((SELECT CAST(MAX(CAST(speed AS DECIMAL))) FROM PC, CHAR(4)) + 'x'

Answer 1

Remove x and store numbers. When selecting, add x when necessary using CONCAT functions

Answer 2

very simple .

SELECT * FROM Test ORDER BY CAST(REPLACE(speed, 'x', '') AS UNSIGNED) DESC LIMIT 1;