MSSQL Functions – Version Delimiter

SQL

IF OBJECT_ID(N'dbo.fnVersionNthPart') IS NOT NULL
    DROP FUNCTION dbo.fnVersionNthPart
GO

CREATE FUNCTION dbo.fnVersionNthPart (
	@version AS NVARCHAR(max),
	@part AS INT
	)
RETURNS INT
AS
BEGIN
	DECLARE @ret AS INT = NULL,
		@start AS INT = 1,
		@end AS INT = 0,
		@partsFound AS INT = 0

	IF @version IS NOT NULL
	BEGIN
		SET @ret = 0

		WHILE @partsFound < @part
		BEGIN
			SET @end = charindex('.', @version, @start)

			IF @end = 0
				SET @partsFound = @part -- bail early
			ELSE
			BEGIN
				SET @partsFound = @partsFound + 1

				IF @partsFound = @part
					SET @ret = Convert(INT, substring(@version, @start, @end - @start))
				ELSE
					SET @start = @end + 1
			END
		END
	END

	RETURN @ret
END

IF OBJECT_ID(N'dbo.fnVersionNthPart') IS NOT NULL
    DROP FUNCTION dbo.fnVersionNthPart
GO

CREATE FUNCTION dbo.fnVersionNthPart (
	@version AS NVARCHAR(max),
	@part AS INT
	)
RETURNS INT
AS
BEGIN
	DECLARE @ret AS INT = NULL,
		@start AS INT = 1,
		@end AS INT = 0,
		@partsFound AS INT = 0

	IF @version IS NOT NULL
	BEGIN
		SET @ret = 0

		WHILE @partsFound < @part
		BEGIN
			SET @end = charindex('.', @version, @start)

			IF @end = 0
				SET @partsFound = @part -- bail early
			ELSE
			BEGIN
				SET @partsFound = @partsFound + 1

				IF @partsFound = @part
					SET @ret = Convert(INT, substring(@version, @start, @end - @start))
				ELSE
					SET @start = @end + 1
			END
		END
	END

	RETURN @ret
END

Reference: Laurence @ http://stackoverflow.com/questions/13315756/how-to-compare-sql-strings-that-hold-version-numbers-like-net-system-version-cl

Originally Posted on September 8, 2015
Last Updated on October 26, 2025

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related