Solved

How to properly sort a varchar column in numeric order

Posted on 2012-03-11
4
440 Views
Last Modified: 2012-03-12
I've been looking for a good solution for the last couple of hours - gotten close but most of what I've seen uses a case to sort integers first and then the varchars or similar. But none actually sort like I want and the things I have seen that purport to work are for Oracle and I'm using MSSQL.

That said, most of my data is int but there are a few rows that have either an 'x' or 'r' or 'c' which causes all sorts of issues with the sort.  a normal sort would return:
1372X
137X
1675X
19412
338X
3508
3543
3549

But, in an ideal world, I would like it to sort numerically - more like:
137X
338X
1372X
1675X
3508
3543
3549
19412

So, I would appreciate some help with this.  The client (or clients in this case) are very insistent that the sort look right - letters or no.  Many thanks in advance as always.
0
Comment
Question by:saabStory
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 37708443
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37708560
try this
declare @T table(id varchar(20))

insert into @T
select '1372X'
union select '137X'
union select '1675X'
union select '19412'
union select '338X'
union select '3508'
union select '3543'
union select '3549'

select *, PATINDEX('%[A-Z]%', id) from @T 
order by case 
		when id is null then
			0
		when PATINDEX('%[A-Z]%', id) > 0 then	
			CAST(LEFT(id, PATINDEX('%[A-Z]%', id)-1) AS int )
		else 
			CAST (id as int)
		end

Open in new window

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 37708565
Here is a cleanedup version

select * 
from YourTable 
order by case 
			when YourField is null then
				0
			when PATINDEX('%[A-Z]%', YourField) > 0 then	
				CAST(LEFT(YourField, PATINDEX('%[A-Z]%', YourField)-1) AS int )
			else 
				CAST (YourField as int)
		end

Open in new window

0
 

Author Closing Comment

by:saabStory
ID: 37709784
Perfect!  Many, many thanks.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question