Click here to Skip to main content
15,906,329 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have a varchar field CaseNo in table which contains the data in this format for eg.
FA/12/2014, FA/11/2015, FA/12/2015, FA/11/2014, CC/12/2015, CC/11/2015.
I wanted to sort the result of select query in the following manner

CC/11/2015
CC/12/2015
FA/11/2014
FA/12/2014
FA/11/2015
FA/12/2015

Firstly it should sort first two characters in alphabetic order. And Then the remaining digits in ascending order and result should be like as above..
Is this possible to do so.
Thanks
Posted

Don't.
It's possible, with a lot of work and a temporary table: Converting comma separated data in a column to rows for selection[^] gives you a start by splitting each entry to a separate row. But it's a poor idea: you should really be using a separate table for each value, with a foreign key back to the relevant row. And then store it in separate fields so your sorting becomes trivial.
When you want to display it, you then add the columns together to produce the data for display and use a JOIN to "reassemble" the data with it's parent row.
 
Share this answer
 
You can use below query to get desired result.

SQL
SELECT columnName  FROM TableName
ORDER BY SUBSTRING(columnName,0,3) ,SUBSTRING(columnName,7,4), SUBSTRING(columnName,4,7)



Note: If you have lots of rows in table, it might have some performance impact. In that case you can keep values in auto calculated columns and apply sorting on that.
 
Share this answer
 
As OriginalGriff pointed out, never store several values in a single column. Doing so will introduce a whole bunch of unnecessary complexity in your program, for example the question you're currently asking.

If you would have three columns, you could easily define the sorting as
SQL
ORDER BY Col1, Col2, Col3

And that's it. No complexity at all.

If, for some reason, you need the database to be able to show the three fields concatenated together, you can always so it in the select statement, use a view or create a computed column.
 
Share this answer
 
Here is the solution
SQL
select * from temp
   order by
   left(columnA,2) asc ,
   right(columnA,4) asc,
   cast(replace(replace(columnA,left(columnA,3),''),right(columnA,5),'') as int) asc
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900