Kermit - 4 months ago 21

SQL 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`

- 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.
- Simply compare values after the decimal (ie. 12 > 8), however this fails when the version rolls to 6.0.
- 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 Source

```
declare @v1 varchar(100) = '5.12'
declare @v2 varchar(100) = '5.8'
select
case
when CONVERT(int, LEFT(@v1, CHARINDEX('.', @v1)-1)) < CONVERT(int, LEFT(@v2, CHARINDEX('.', @v2)-1)) then 'v2 is newer'
when CONVERT(int, LEFT(@v1, CHARINDEX('.', @v1)-1)) > CONVERT(int, LEFT(@v2, CHARINDEX('.', @v2)-1)) then 'v1 is newer'
when CONVERT(int, substring(@v1, CHARINDEX('.', @v1)+1, LEN(@v1))) < CONVERT(int, substring(@v2, CHARINDEX('.', @v2)+1, LEN(@v1))) then 'v2 is newer'
when CONVERT(int, substring(@v1, CHARINDEX('.', @v1)+1, LEN(@v1))) > CONVERT(int, substring(@v2, CHARINDEX('.', @v2)+1, LEN(@v1))) then 'v1 is newer'
else 'same!'
end
```