create table #AlphaNum
(
 PKCol int IDENTITY (1, 1) primary key,
 NumericStr varchar (5)
)
go

insert into #AlphaNum values ('1')
insert into #AlphaNum values ('1A')
insert into #AlphaNum values ('2')
insert into #AlphaNum values ('2AB')
insert into #AlphaNum values ('3')
insert into #AlphaNum values ('30')
insert into #AlphaNum values ('4abc')
go
select * from #AlphaNum
order by convert( int , 
         case
           -- If no numeric digits , then NULL
           -- Or use whatever value u want
           -- You can use ISNUMERIC also but this doesn't
           -- handle characters like ',' or '.' well.
           when patindex('%[0-9]%' , NumericStr) = 0
                then NULL

           -- Get the first non-numeric char. this assumes
           -- the format 'nnnAAAA'
           -- where A represents non-numeric digit
           when patindex('%[^0-9]%' , NumericStr) > 0
                then substring( NumericStr , 1 , patindex('%[^0-9]%' , NumericStr) - 1 )

           -- this is a completely numeric value.
           else NumericStr
         end )
go
drop table #AlphaNum
go

This page was last updated on May 01, 2006 04:28 PM.