SQL statement query maximum problem

liuwei09314

Table RUNCARD has M, N and other fields
The data in M ​​such as M(1)=mm001
             M(2)=mm002
             M(3)=mm003
             ......
    Type varchar
Now I want to make a loop, extract the maximum value from the number part after mm in M, and attach it to num, the type is integer

Can you please take a look at my sentence?
SELECT MAX(CAST(SUBSTRING(M,2,9) AS INTEGER)) AS NUM FROM RUNCARD

I feel that the type conversion is a bit messy, please expert advice, thank you, urgent! ! !

michealhenry

SELECT MAX(CAST(SUBSTRING(M,3,9) AS INT)) AS NUM FROM RUNCARD

lancelover

That's right,michealhenry, that's right

hiqwolfzzh

this is okay too:)
SELECT MAX(cast(REPLACE(M,'mm','') AS INT)) AS NUM FROM RUNCARD

fenghui1231

this is okay too
select cast(right((select max(m) from runcard),3) as int) as num