Binaryworld - Sorting only based on the numeric digits in a column containing alpha- ... [ SQL Server -> T-SQL ]

An example for using a CASE expression in an ORDER BY clause. This solution solves the problem of sorting based on the numeric values in a column. The column contains strings of the format 'nnnXXXX' where 'n' represents a numeric digit & 'X' represents any non-numeric character.

Click here to copy the following block

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