How to custom sort this data in SQL Server 2012?

Question

I have some hard time figuring it out how to custom sort data below the way I want to. Meaning it should be in order like this:

201-1-1
201-1-2
201-1-3
.......
201-2-1

and so on if you know what I mean.

Instead I'm getting this sort executing below code:

select * 
from test.dbo.accounts 
order by account_name asc

Output:

201-10-1
201-10-2
201-1-1
201-11-1
201-11-2
201-11-3
201-11-4
201-11-6
201-1-2
201-12-1
201-12-2
201-12-3
201-12-4
201-12-6
201-1-3
201-13-1
201-13-2
201-13-3
201-13-4
201-13-6
201-1-4
201-14-1
201-14-2
201-14-4
201-14-6
201-15-1
201-15-2
201-15-3
201-15-4
201-15-6
201-1-6
201-16-1
201-16-2
201-16-3
201-16-4
201-16-6
201-16-7
201-1-7
201-17-1
201-17-2
201-17-4
201-17-6
201-18-1
201-18-2
201-18-3
201-18-4
201-18-6
201-19-1

Thanks

Gordon Linoff · Accepted Answer · 2015-09-05 22:22:55Z

3

For your sample data, this following trick will work:

order by len(account_name), account_name

This only works because the only variable length component is the second component and because the hyphen is "smaller" than digits.

You should normalize the accounts names so all the components are the same length, by left padding the numbers with zeros.

answered Sep 5, 2015 at 22:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rwking · Accepted Answer · 2015-09-05 22:16:08Z

2

Ugh. String manipulation in SQL can be extremely cumbersome. There might be a better way to do this, but this does seem to work.

select accoutn_name
from test.dbo.accounts
order by left(account_name,charindex('-',account_name,1)-1)
                        ,replace(right(left(account_name,CHARINDEX('-',account_name,1)+2),2),'-', '')
                        ,REPLACE(right(account_name,2),'-','')

BTW, this is a very expensive process to run. If it's productionalized, you'll want to come up with a better solution.

edited Sep 5, 2015 at 22:16

answered Sep 5, 2015 at 22:02

rwking

1,0326 silver badges18 bronze badges

1 Comment

Zulu Z Over a year ago

sorry, actually your solution was proper. I didn't notice at first that the other one did not group properly. thanks

Collectives™ on Stack Overflow

How to custom sort this data in SQL Server 2012?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related