how to perform a max() on an alphanumeric column correctly

I have a table for users where the IDs are such: ASmith1, Asmith2, Asmith3, Asmith5, Asmith9,开发者_开发知识库 Asmith10, Asmith13, Asmith15, Asmith21, etc.

When I get the Max() of that column, I get Asmith9. What query or function can I use to get Asmith21?

   declare @Users table(ID varchar(10));
    insert into @Users
        select  'ASmith1' union all 
        select  'Asmith2' union all 
        select  'Asmith3' union all 
        select  'Asmith5' union all 
        select  'Asmith9' union all 
        select  'Asmith10' union all 
        select  'Asmith13' union all 
        select  'Asmith15' union all 
        select  'Asmith21'

    select  top 1 ID
    from    @Users
    order
    by      len(ID) desc, ID desc;

If you have control over the names you can suffix with numbers using enough leading zeros for your needs. Failing that you'd need to split the numeric part from the alpha part and sort accordingly.

SQL Server doesn't have regex out of the box but you can create a function for it as shown in this article that takes advantage of SQL Server's access to .Net:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

There is no possible solutions directly, However you can store fixed length strings then it is possible like:

Asmith001, Ashmith002, Ashmit021, Ashmit100

The other method is using string functions to achieve like this in query and then you can order.