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;

Answer 2

It's still treating your value as a string instead of a number. Try:

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

Answer 3

Because you're still comparing strings. The fact that they contain only numeric digits doesn't mean that they're not strings. You need to convert them:

SELECT MAX(CAST(REPLACE(id, 'XYZ', '') AS INT)) FROM My_Table

Answer 4

Another method is

select max(REPLACE(ID,'XYZ','')*1) from myTable