Search code examples
sql-servert-sqlmax

select max value from a string column in SQL

I have table with a column Order of type varchar as follows

Order
-----
Ord-998,
Ord-999,
Ord-1000,
Ord-1001,

I want to get the max value as 1001

But when I run this query, I am getting 999 as max value always

select 
    SUBSTRING((select isnull(MAX(OrderNo), '0000000') 
               from OrderSummary 
               where OrderNo like 'Ord%'), 5, 10) as [OrderMax]

Can anybody provide a solution?

Solution

  • Since you are maxing a string it is sorting alphabetically where C is larger than AAA and 9 is larger than 10. Remove the letters and cast it as an int then get the max. Given that it will always be Ord-### we can remove the Ord- and cast the remainder as an INT.

    SELECT
    MAX(CAST(SUBSTRING(OrderNo,5,LEN(OrderNo)-4) AS INT))
FROM OrderSummary
WHERE OrderNo LIKE 'Ord-%'