I want to find maximum number in sql server i have values with string part and integer part

Question

I have values like SP-1,SP-2,SP-3.....SP-10,SP-11.

I have to get the maximum number from here in my SQL server

SELECT MAX(SUBSTRING(SupplementId,4,10)) AS max_num FROM supreg

When I execute the code I get the maximum number as 9.

Answer 1

Try:

SELECT MAX(CAST(SUBSTRING(SupplementId,4,10) AS INT)) AS max_num FROM supreg

or

SELECT MAX(CAST(REPLACE(SupplementId,'SP-','') AS INT)) AS max_num FROM supreg