0

I have the following table strand which is ordered in ascending order except for the 1st value,

 Strand
 3.1.1
 3.1.1.1
 3.1.1.2
 3.1.2
 3.1.2.1
 3.1.2.2
 3.10       # this should have been at the top most
 3.2.1
 3.2.1.1
 ...
 ...
 3.20      #Similarly,this should have been above 3.2.1 and below 3.1.2.2

The column is in varchar and i think converting the 3.10 to 3.1 and 3.20 to 3.2 will do the trick i.e converting from varchar to decimal(1,1) only for those values, but I'm not getting how to convert or cast it for particular fields inside a column...i dont want to cast the entire column..i wont get the answer then...

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
goonerboi
  • 309
  • 6
  • 18

2 Answers2

0

You can include an order by length.

ORDER BY
LEFT(strand,1),
RIGHT(LEFT(strand,3),1),
CASE WHEN RIGHT(LEFT(strand,4),1) <> '.' and RIGHT(LEFT(strand,4),1) <> '' THEN 1 ELSE 2 END

With this, shorter strings will appear first

beejm
  • 2,381
  • 1
  • 10
  • 19
  • but i dont want all short strings to appear first..check the conditions i have mentioned in hashtags for 3.10 and 3.20 – goonerboi Mar 21 '17 at 05:33
0

Try this

select * from @t1
ORDER BY 
CASE WHEN strand like '%0%' then convert(varchar,CONVERT(float,strand)) ELSE strand END

enter image description here

SimarjeetSingh Panghlia
  • 2,200
  • 13
  • 14