|
|
|
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 when patindex('%[0-9]%' , NumericStr) = 0 then NULL
when patindex('%[^0-9]%' , NumericStr) > 0 then substring( NumericStr , 1 , patindex('%[^0-9]%' , NumericStr) - 1 )
else NumericStr end ) go drop table #AlphaNum go |
|
|
|
Submitted By :
Nayan Patel
(Member Since : 5/26/2004 12:23:06 PM)
|
|
|
Job Description :
He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting. |
View all (893) submissions by this author
(Birth Date : 7/14/1981 ) |
|
|