3


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..
Note:- last part of the data is year but middle part is not a month it is just a number.
Is this possible to do so.
Thanks

c.jack
  • 375
  • 1
  • 3
  • 18
  • Let see relative question [here][1], Hope this help! [1]: http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – Giau Huynh Jul 22 '15 at 09:04
  • thanks that solution is related to spliting the field data but i wanted to sort the data on the basis of parts of the field data – c.jack Jul 22 '15 at 10:01

4 Answers4

3

This is probably what you're looking for:

select * from table1 
order by left(col1, 2), convert(date, '01/' + substring(col1, 4, 7), 103)

My assumption was that the last part is month + year, but of course it could be something else too.

James Z
  • 12,209
  • 10
  • 24
  • 44
2

You must do this :

    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

And a DEMO here ! So you edited the post and this should work just fine. This is not converting into Date the last part of the string

danvasiloiu
  • 751
  • 7
  • 24
  • Thanks now first part and last part are sorting good but middle part is still creating problem have a look
    following is the result
    FA/7/2015 FA/8/2015 FA/9/2015 FA/100/2015 FA/600/2015 FA/500/2015 FA/200/2015 FA/700/2015 FA/701/2015 FA/201/2015 FA/501/2015 FA/601/2015 FA/101/2015 FA/102/2015 FA/602/2015 FA/502/2015 FA/702/2015 FA/202/2015 FA/203/2015 FA/703/2015 FA/503/2015 FA/603/2015 FA/103/2015 FA/104/2015 FA/604/2015 FA/504/2015 FA/204/2015     – c.jack Jul 22 '15 at 09:41
  • Ha sry i didn't knew that the middle number can be bigger then 99 – danvasiloiu Jul 22 '15 at 09:43
  • very near to solution. now getting result as follows FA/1/2014 FA/10/2014 FA/11/2014, FA/19/2014 FA/2/2014 FA/20/2014 FA/21/2014 FA/22/2014 FA/23/2014, FA/3/2014 FA/4/2014, FA/8/2014 FA/9/2014 FA/1/2015 FA/10/2015 FA/100/2015 FA/101/2015 – c.jack Jul 22 '15 at 10:07
  • Little change (using CAST)in your query make it to work as exactly that i needed following is the solution,
    order by left(CaseNo_ID,2) asc ,right(CaseNo_ID,4) asc, CAST(( replace(replace(CaseNo_ID,left(CaseNo_ID,3),''),right(CaseNo_ID,5),'')) as int) asc     – c.jack Jul 22 '15 at 10:18
  • ok. right. updated the answer. you can mark it as solution now. – danvasiloiu Jul 22 '15 at 10:27
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83959/discussion-between-ankitsrist-and-dzomba). – c.jack Jul 22 '15 at 10:39
1

Try this order by. More generic way

ORDER  BY LEFT ('FA/12/2014', Charindex('/', 'FA/12/2014') - 1),
          Cast('01'+ Substring('FA/12/2014', Charindex('/', 'FA/12/2014'), 8) AS DATE)

DEMO

SELECT *
FROM   (VALUES ('CC/11/2015'),
               ('CC/12/2015'),
               ('FA/11/2014'),
               ('FA/12/2014'),
               ('FA/11/2015'),
               ('FA/12/2015')) tc (dates)
ORDER  BY LEFT (dates, Charindex('/', dates) - 1),
          Cast('01'
               + Substring(dates, Charindex('/', dates), 8) AS DATE)

Result :

dates
----------
CC/11/2015
CC/12/2015
FA/11/2014
FA/12/2014
FA/11/2015
FA/12/2015
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

I think you can use PARSENAME in this case

SELECT *
FROM   #Your_Table
ORDER  BY Parsename(Replace(columnA, '/', '.'), 3),
          Parsename(Replace(columnA, '/', '.'), 1),
          Parsename(Replace(columnA, '/', '.'), 2)
PP006
  • 681
  • 7
  • 17