Malinda Peiris Malinda Peiris - 3 months ago 8
SQL Question

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

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

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