T-SQL Table Valued Function to compare Semantic Versions
A semantic version is a standardized way to label software releases using a three-part number: major, minor, and patch (for example, 1.2.3). This system is defined by the Semantic Versioning 2.0.0 specification (SemVer), which dictates that each segment conveys meaning: the major version changes for incompatible API updates, the minor version for backward-compatible feature additions, and the patch version for backward-compatible bug fixes. Semantic versions may also include optional pre-release labels (like -beta or -rc1) and build metadata (like +build.123), which provide additional context about the release.
Comparing semantic versions is challenging because it involves more than simple numeric comparison. While the major, minor, and patch numbers can be compared numerically, pre-release labels introduce lexical (alphabetical) and numeric ordering rules, and stable releases always take precedence over pre-releases. Additionally, build metadata is ignored for ordering purposes. As a result, robust comparison logic must account for both numeric and string components, as well as the special precedence rules defined by the SemVer standard, making direct string or integer comparison insufficient for correct version ordering.
The SQL Server hierarchyid data type is ideally suited for comparing the numeric components of semantic versions because it naturally represents hierarchical, dot-separated values like major.minor.patch. By converting a version string such as 1.2.3 into a hierarchy path (/1/2/3/), hierarchyid enables efficient, accurate, and intuitive ordering and comparison of version numbers without the need for complex string parsing or manual zero-padding. This approach avoids the pitfalls of lexicographical string comparison (where, for example, 1.10.0 would incorrectly sort before 1.2.0), and eliminates the need for cumbersome arithmetic encoding or splitting and casting each segment individually.
CREATE or ALTER FUNCTION dbo.CompareSemVer
(
@VersionA varchar(50),
@VersionB varchar(50)
)
/*
Compares two semantic version strings (e.g., '1.2.3', '1.2.3-preview1').
Returns 1 if VersionA > VersionB,
0 if VersionA = VersionB,
-1 if VersionA < VersionB,
following SemVer precedence rules (numeric parts, then pre-release label and number).
*/RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH
-- Parse VersionA
ParsedA AS (
SELECT
-- Numeric part (before '-')
LEFT(@VersionA, ISNULL(NULLIF(CHARINDEX('-', @VersionA), 0) - 1, LEN(@VersionA))) AS NumericA,
-- Pre-release part (after '-')
CASE WHEN CHARINDEX('-', @VersionA) > 0 THEN 1 ELSE 0 END AS HasPreA,
CASE WHEN CHARINDEX('-', @VersionA) > 0 THEN
LEFT(
SUBSTRING(@VersionA, CHARINDEX('-', @VersionA) + 1, LEN(@VersionA)),
PATINDEX('%[0-9]%', SUBSTRING(@VersionA, CHARINDEX('-', @VersionA) + 1, LEN(@VersionA)) + 'a') - 1
)
END AS PreLabelA,
CASE WHEN CHARINDEX('-', @VersionA) > 0 THEN
TRY_CAST(
SUBSTRING(
SUBSTRING(@VersionA, CHARINDEX('-', @VersionA) + 1, LEN(@VersionA)),
PATINDEX('%[0-9]%', SUBSTRING(@VersionA, CHARINDEX('-', @VersionA) + 1, LEN(@VersionA)) + 'a'),
LEN(@VersionA)
) AS INT
)
END AS PreNumA
),
-- Parse VersionB
ParsedB AS (
SELECT
LEFT(@VersionB, ISNULL(NULLIF(CHARINDEX('-', @VersionB), 0) - 1, LEN(@VersionB))) AS NumericB,
CASE WHEN CHARINDEX('-', @VersionB) > 0 THEN 1 ELSE 0 END AS HasPreB,
CASE WHEN CHARINDEX('-', @VersionB) > 0 THEN
LEFT(
SUBSTRING(@VersionB, CHARINDEX('-', @VersionB) + 1, LEN(@VersionB)),
PATINDEX('%[0-9]%', SUBSTRING(@VersionB, CHARINDEX('-', @VersionB) + 1, LEN(@VersionB)) + 'a') - 1
)
END AS PreLabelB,
CASE WHEN CHARINDEX('-', @VersionB) > 0 THEN
TRY_CAST(
SUBSTRING(
SUBSTRING(@VersionB, CHARINDEX('-', @VersionB) + 1, LEN(@VersionB)),
PATINDEX('%[0-9]%', SUBSTRING(@VersionB, CHARINDEX('-', @VersionB) + 1, LEN(@VersionB)) + 'a'),
LEN(@VersionB)
) AS INT
)
END AS PreNumB
),
-- Final comparison
Final AS (
SELECT
@VersionA AS VersionA,
@VersionB AS VersionB,
-- Numeric parts for hierarchyid
REPLACE(a.NumericA, '.', '/') AS NumericAPath,
REPLACE(b.NumericB, '.', '/') AS NumericBPath,
a.HasPreA, a.PreLabelA, a.PreNumA,
b.HasPreB, b.PreLabelB, b.PreNumB,
-- Comparison logic
CASE
WHEN CAST('/' + REPLACE(a.NumericA, '.', '/') + '/' AS hierarchyid) < CAST('/' + REPLACE(b.NumericB, '.', '/') + '/' AS hierarchyid) THEN -1
WHEN CAST('/' + REPLACE(a.NumericA, '.', '/') + '/' AS hierarchyid) > CAST('/' + REPLACE(b.NumericB, '.', '/') + '/' AS hierarchyid) THEN 1
ELSE
CASE
-- Stable > pre-release
WHEN a.HasPreA = 0 AND b.HasPreB = 1 THEN 1
WHEN a.HasPreA = 1 AND b.HasPreB = 0 THEN -1
-- Both stable
WHEN a.HasPreA = 0 AND b.HasPreB = 0 THEN 0
-- Both pre-release: compare label
WHEN a.PreLabelA < b.PreLabelB THEN -1
WHEN a.PreLabelA > b.PreLabelB THEN 1
-- Labels equal: compare number
WHEN ISNULL(a.PreNumA, 0) < ISNULL(b.PreNumB, 0) THEN -1
WHEN ISNULL(a.PreNumA, 0) > ISNULL(b.PreNumB, 0) THEN 1
ELSE 0
END
END AS VersionCompare
FROM ParsedA a
CROSS JOIN ParsedB b
)
SELECT
VersionA,
VersionB,
NumericAPath,
NumericBPath,
HasPreA,
PreLabelA,
PreNumA,
HasPreB,
PreLabelB,
PreNumB,
VersionCompare
FROM Final;
go
CREATE TABLE #VersionTest (
VersionA varchar(50),
VersionB varchar(50));
go
INSERT INTO #VersionTest (VersionA, VersionB) VALUES
('1.2.3', '1.2.3'), -- equal
('1.2.3', '1.2.4'), -- patch
('1.2.3', '1.3.0'), -- minor
('2.0.0', '1.9.9'), -- major
('1.2.3-preview1', '1.2.3-preview2'), -- pre-release number
('1.2.3-preview2', '1.2.3-rc1'), -- pre-release label
('1.2.3', '1.2.3-preview1'), -- stable vs pre-release
('1.2.3-preview1', '1.2.3'), -- pre-release vs stable
('1.2.3-rc1', '1.2.3-rc1'), -- equal pre-release
('1.2.3-rc1', '1.2.3-rc2'), -- pre-release number
('1.2.3-preview1', '1.2.3-preview1'); -- equal pre-release
SELECT
t.VersionA,
t.VersionB,
f.VersionCompare
FROM #VersionTest t
CROSS APPLY dbo.CompareSemVer(t.VersionA, t.VersionB) f
ORDER BY t.VersionA, t.VersionB; 
