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

Improve this question
2
  • Let see relative question [here][1], Hope this help! [1]: stackoverflow.com/questions/5493510/… Commented Jul 22, 2015 at 9: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 Commented Jul 22, 2015 at 10:01

4 Answers 4

Reset to default
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.

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

1 Comment

thanks actually last part is year but middle part is just a number not month.
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

Improve this answer

6 Comments

Thanks now first part and last part are sorting good but middle part is still creating problem have a look <br/> following is the result<br/> 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
Ha sry i didn't knew that the middle number can be bigger then 99
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
Little change (using CAST)in your query make it to work as exactly that i needed following is the solution, <br/> 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
ok. right. updated the answer. you can mark it as solution now.
|
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
Improve this answer

4 Comments

why do you chose the most complicated way to do it ?
@dzomba - No. Its not about complicated its more generic. Tomorrow instead of CC if CCC comes also this will work.
"Note:- last part of the data is year but middle part is not a month it is just a number." so your answer is not good.
middle part is not a month but just a number
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)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.