January 21, 2012

How to get Max value from a string

One of my table contains a column 'RefNumber' which is varchar field. Last part of the field contains a number. To get the max value of the number use below Sql.


SELECT ISNULL(MAX(CAST(RIGHT(RefNumber, CHARINDEX( '/', 
REVERSE(RefNumber)) - 1) AS INT)), 0) + 1 AS MaxRefNumber 
FROM [TABLE1] 




RefNumber
--------------
MECIT/ACT/12
EVO/ACT/IT/13
MECIT/HR/14
EVO/ACT/HR/15


The above sql will return the value 16

No comments:

Post a Comment