I was looking for a function like that a while ago to help me identify UK car registrations that were issued consecutively (our company buys vehicles in bulk and sometimes registers a bunch together). Here is my modification of the function posted earlier, handling upper and lower case, and numbers, with an extra parameter for determining whether numeric characters are issued 0,1,...,8,9 or 1,2,...9,0

CREATE FUNCTION [dbo].[fn_NextConsecutiveString] (

@String varchar(32) --would need to use some other kind of looping/recursion for strings longer than 32 characters, but this kind of function not likely to be needed for massive strings

,@NumberIssuedFirst tinyint --set to 0 or 1. If parameter set to 0 will consider 0 was the first number issued so will flip from 9 to 0 and continue recursion. If parameter set to 1 will flip from 0 to 1 and continue recursion, and will increment from 9 to 0 without further recursion. Probably not really relevant for UK car registrations , as the numbers relate to the semester rather than something that increments automatically for uniqueness. Presumably within each reg area (first two chars) and six month period (second two chars) they never need more unique values than AAA to ZZZ (last three chars), so it won't arise anyway.

)

RETURNS varchar(32)

AS

-- example usage:

-- SELECT dbo.fn_NextConsecutiveString('VX69EZZ',0) would return 'VX69FAA'

-- SELECT dbo.fn_NextConsecutiveString('VX69ZZZ',0) would return 'VX70AAA'

-- SELECT dbo.fn_NextConsecutiveString('VX99ZZZ',0) would return 'VY00AAA'

-- SELECT dbo.fn_NextConsecutiveString('VX99ZZZ',1) would return 'VX90AAA'

-- SELECT dbo.fn_NextConsecutiveString('vx90zZz',1) would return 'vx01aAa'

-- SELECT dbo.fn_NextConsecutiveString('VX10ZZZ',1) would return 'VX21AAA'

--this function is recursive, the depth of recursion will relate to how many characters it has to change

BEGIN

IF @NumberIssuedFirst NOT IN (0,1) RETURN cast('Error: When using function dbo.fn_NextConsecutiveString the second parameter @NumberIssuedFirst must be either 0 or 1' as int);

DECLARE @C smallint,@Return varchar(32), @Len smallint

SELECT @C=ASCII(RIGHT(@String,1))

SELECT @Len=LEN(@String)

SET @Return=CASE WHEN @C IN (90,122) --ends with 'Z' or 'z'

THEN CASE WHEN @Len=1

THEN CHAR(@C-25)

ELSE dbo.fn_NextConsecutiveString(LEFT(@String,LEN(@String)-1), @NumberIssuedFirst)+CHAR(@C-25) --changing last character to 'A' or 'a'

END

WHEN @NumberIssuedFirst=0 AND @C=57 --ends with 9 and 0 was issued first so we will flip from 9 to 0 (and continue recursion if more characters left)

THEN CASE WHEN @Len=1

THEN '0'

ELSE dbo.fn_NextConsecutiveString(LEFT(@String,LEN(@String)-1), @NumberIssuedFirst)+'0'

END

WHEN @NumberIssuedFirst=1 AND @C=48 --ends with 0 and 1 was issued first so we will flip from 0 to 1 (and continue recursion if more characters left)

THEN CASE WHEN @Len=1

THEN '1'

ELSE dbo.fn_NextConsecutiveString(LEFT(@String,LEN(@String)-1), @NumberIssuedFirst)+'1'

END

WHEN @NumberIssuedFirst=1 AND @C=57 --ends with 9 and 1 was issued first so we will increment from 9 to 0 (effectively acting like 10) without further recursion

THEN LEFT(@String,LEN(@String)-1)+'0'

ELSE --increment final character

LEFT(@String,LEN(@String)-1)+CHAR(@C+1)

END

RETURN @Return

END