x

Order By ... ASC, but Alpha before Numeric

I am trying to use ORDER BY to order column data that I am working with. My column data looks something like this.

VP717X VP7865 17C831 AP4R2Q

I need to sort this data in ascending order. The only problem is that Alpha characters need to come before numeric characters. So yea...completely bizzaro world. Is there a way I can do this and still use a SELECT statement with an ORDER BY clause.

Thanks in advance for your help,

JZ

more ▼

asked Jan 05, 2010 at 11:16 AM in Default

avatar image

Jim Czerniak
24 4 4 5

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first

As TG said, but I tend to prefer a pseudo column to set the primary ordering (Alpha / Numeric in this example) and then sort by the column as a secondary, so:

            
ORDER BY CASE WHEN MyCol LIKE '[0-9]%' THEN 2 ELSE 1 END,            
         MyCol            
more ▼

answered Jan 05, 2010 at 01:36 PM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left

Try this ORDER BY clause:

select col            
from   (            
       select 'VP717X' as col             
       union all select  'VP7865'             
       union all select  '17C831'             
       union all select 'AP4R2Q'            
       ) as yourTable            
order by             
       case             
       when left(col, 1) like '[0-9]' then 'z' + col             
       else '0'+ col             
       end            

OUTPUT:

col            
------            
AP4R2Q            
VP717X            
VP7865            
17C831            
more ▼

answered Jan 05, 2010 at 12:03 PM

avatar image

TG
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x511
x28

asked: Jan 05, 2010 at 11:16 AM

Seen: 5066 times

Last Updated: Jan 05, 2010 at 01:36 PM

Copyright 2017 Redgate Software. Privacy Policy