-4

I have a varchar column containing

0.1
0.8
0.2
Drr
0.11
0.8.1
0.8.2
0.2.1

How do I sort it to get results like this please?

0.1
0.2
0.2.1
0.8
0.8.1
0.8.2
0.11
Drr

Thanks

PK20
  • 1,066
  • 8
  • 19
pbannist
  • 26
  • 3
  • 4
    possible duplicate of [http://stackoverflow.com/questions/119730/how-do-i-sort-a-varchar-column-in-sql-server-that-contains-numbers](http://stackoverflow.com/questions/119730/how-do-i-sort-a-varchar-column-in-sql-server-that-contains-numbers) – PK20 Nov 25 '15 at 09:03
  • 4
    Tag dbms used. (Product specific functionality may be needed here.) – jarlh Nov 25 '15 at 09:05
  • 1
    Having strings where the query needs to know about its composition is usually a sign of bad database design. So you may want to think over your design. If you stick to this, it boils down to string manipulation. That shouldn't be too hard to do. I suggest you try it yourself and come back in case you get stuck. – Thorsten Kettner Nov 25 '15 at 09:58
  • 1
    Looks like a versioning system to me. Could put each minor and sub-minor version in its own column, then do a simple order by. Let me know if you want an example. – A. Greensmith Nov 25 '15 at 10:05
  • 1
    if anyone is interested this article provided the solution I needed http://sqlmag.com/sql-server/solutions-t-sql-sorting-challenge – pbannist Nov 25 '15 at 17:32

1 Answers1

1

you may try this:

WITH isContainAlpha AS
(
    SELECT  *,
            CASE WHEN version_no LIKE '%[a-z]%' THEN 1 ELSE 0 END AS withAlpha,
            CASE WHEN version_no LIKE '%[a-z]%' THEN '0' ELSE version_no END AS version_no_rewrite
    FROM tableOne
)
SELECT version_no
FROM isContainAlpha
ORDER BY withAlpha, CAST('/'+REPLACE(version_no_rewrite,'.','/')+'/' as hierarchyID)
Harlo
  • 507
  • 2
  • 12