Problem with max() in sql server

Question

I have alphanumeric values like. XYZ1,XYZ2......XYZ11, XYZ12 and so on, now I want to select only the Max numeric value, i.e. 12 here. I tried-

 select max(REPLACE(ID,'XYZ','')) from myTable;

but this is returning 9. why?

Answer 1

Try converting to INT before max

select max(cast(REPLACE(ID,'XYZ','') as int)) from myTable;