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

Comments

Popular posts from this blog

മലയാളത്തില്‍ മൊത്തം എത്ര അക്ഷരങ്ങള്‍ ഉണ്ട്?

Convert Number To Words in SQL Sever

Crystal reports load report failed: Could not load file or assembly CrystalDecisions.Web, Version=10.2.3600.0