How to compare software versions using SQL Server?

Question

When trying to compare software versions 5.12 to 5.8, version 5.12 is newer, however mathematically 5.12 is less than 5.8. How would I compare the two versions so that a newer version returns 'Y'?

SELECT CASE WHEN 5.12 > 5.8 THEN 'Y' ELSE 'N' END

Possible Solutions

1. Add a 0 after the decimal in 5.8 so that it compares 5.08 to 5.12, however it seems like this would require a bit of code.
2. Simply compare values after the decimal (ie. 12 > 8), however this fails when the version rolls to 6.0.
3. Use reverse logic and assume that if 5.12 is less than 5.8 to return 'Y'. I believe this would fail when the version rolls to 6.0.

Answer 1

You could use hierarchyid Which you can use by putting a / at the end and start of the string and casting it

e.g.

SELECT CASE WHEN cast('/5.12/' as hierarchyid) > cast('/5.8/' as hierarchyid) THEN 'Y' ELSE 'N' END

That returns a Y