Sort on Alphanumeric values

I have seen so many post where developers face problem in sorting the data on alphanumeric values.The most simpls way to do is to use Isnumeric function (applicable for SQL 2005 and beyond).
Here is one of the solutions.

Declare @tbl as table(id int identity(1,1),value varchar(10))
insert into @tbl
select '123AS' union all
select '1245' union all
select 'ABC123AS' union all
select 'WE123AS' union all
select 'AS123AS' union all
select '123AS' union all
select '1'

select * from @tbl
order by isnumeric(value)desc,len(value)

The order will be descending on pure numeric values .

If you need it to be ascending then simply use

select * from @tbl
order by isnumeric(value),len(value) desc

My name is Sachin Nandanwar. I like to think I speak for the average developer. Here's a few facts about me:

• My current focus is in Microsoft .NET, primarily in Web development using ASP.NET but currently putting more of my effort into database developement.
• My database of choice is SQL Server 2005 & 2008. I also know Microsoft Access very well and have dabbled in MySQL. The first database I used on the PC was R:Base.
• I program in both C# and VB. I also programmed in VB 6.0 and JavaScript.
  
• I dont beleive in degrees because they hardly teach you anything practical in life.But still I have a bachelors degree in Mathematcis. I was born in Amchi Mumbai.
• The only website I really really love to haunt is www.sqlteam.com.
• Like body building & was a fitness trainer before fully concentrating into IT.
• I am married to a beautiful wife
• If God willing hope to write a book on Database Development and become a SQL Server MVP.

Thanks for reading my blog.

Sachin Nandanwar