Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

How to properly sort a varchar column in numeric order

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
saabStory
Asked:
saabStory
  • 2
1 Solution
 
Kamaraj SubramanianApplication Support AnalystCommented:
0
 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
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
 
saabStoryAuthor Commented:
Perfect!  Many, many thanks.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now