Μια χρήσιμη συνάρτηση για MS SQL Server που παίρνει σαν όρισμα ενα string που περιέχει το version ενός αντικειμενου και επιστρέφει σε string μορφή semantic την έκδοση.
Με τον τρόπο αυτό μπορεί να γίνει συγκριση ανάμεσα σε 2 εκδόσεις
CREATE FUNCTION [dbo].[SemanticVersion] (
@Version nvarchar(50)
) RETURNS nvarchar(255)
AS BEGIN DECLARE @hyphen int = CHARINDEX('-', @version)
SET
@Version = REPLACE(@Version, '*', ' ') DECLARE @left nvarchar(50) = CASE @hyphen WHEN 0 THEN @version ELSE SUBSTRING(@version, 1, @hyphen -1) END,
@right nvarchar(50) = CASE @hyphen WHEN 0 THEN NULL ELSE SUBSTRING(@version, @hyphen + 1, 50) END,
@normalized nvarchar(255) = '',
@buffer int = 8 WHILE CHARINDEX('.', @left) > 0 BEGIN
SET
@normalized = @normalized + CASE ISNUMERIC(
LEFT(
@left,
CHARINDEX('.', @left)-1
)
) WHEN 0 THEN LEFT(
@left,
CHARINDEX('.', @left)-1
) WHEN 1 THEN REPLACE(
STR(
LEFT(
@left,
CHARINDEX('.', @left)-1
),
@buffer
),
SPACE(1),
'0'
) END + '.'
SET
@left = SUBSTRING(
@left,
CHARINDEX('.', @left)+ 1,
50
) END
SET
@normalized = @normalized + CASE ISNUMERIC(@left) WHEN 0 THEN @left WHEN 1 THEN REPLACE(
STR(@left, @buffer),
SPACE(1),
'0'
) END
SET
@normalized = @normalized + '-' IF (@right IS NOT NULL) BEGIN WHILE CHARINDEX('.', @right) > 0 BEGIN
SET
@normalized = @normalized + CASE ISNUMERIC(
LEFT(
@right,
CHARINDEX('.', @right)-1
)
) WHEN 0 THEN LEFT(
@right,
CHARINDEX('.', @right)-1
) WHEN 1 THEN REPLACE(
STR(
LEFT(
@right,
CHARINDEX('.', @right)-1
),
@buffer
),
SPACE(1),
'0'
) END + '.'
SET
@right = SUBSTRING(
@right,
CHARINDEX('.', @right)+ 1,
50
) END
SET
@normalized = @normalized + CASE ISNUMERIC(@right) WHEN 0 THEN @right WHEN 1 THEN REPLACE(
STR(@right, @buffer),
SPACE(1),
'0'
) END END ELSE
SET
@normalized = @normalized + 'zzzzzzzzzz' RETURN @normalized END
