Here is a stab at this issue as a function. If you pad the input with your zero symbol, you will get padded output, but it will grow the "number" beyond the bounds of the padding up to the length defined for the @Ticket input. Thus, 'ZZ' in returns '100' while '00ZZ' in returns '0100'.

CREATE FUNCTION dbo.NextTicket( @Ticket VARCHAR(128) ) RETURNS VARCHAR(128) AS

BEGIN

DECLARE @digits TABLE( value INT IDENTITY(0,1) NOT NULL PRIMARY KEY, symbol CHAR(1) NOT NULL UNIQUE );

DECLARE @place INT, @zero_digit CHAR(1), @highest_digit CHAR(1);

-- 1. build a table of symbols that compose the "digits" of our ticket numbers

-- Note that you could impose artifical order, it just happens that sorting by

-- the value gives me the order I want.

INSERT INTO @digits ( symbol )

SELECT ch

FROM (

SELECT '0' AS ch UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5'

UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT 'A' UNION SELECT 'B'

UNION SELECT 'C' UNION SELECT 'D' UNION SELECT 'E' UNION SELECT 'F' UNION SELECT 'G' UNION SELECT 'H'

UNION SELECT 'I' UNION SELECT 'J' UNION SELECT 'K' UNION SELECT 'L' UNION SELECT 'M' UNION SELECT 'N'

UNION SELECT 'O' UNION SELECT 'P' UNION SELECT 'Q' UNION SELECT 'R' UNION SELECT 'S' UNION SELECT 'T'

UNION SELECT 'U' UNION SELECT 'V' UNION SELECT 'W' UNION SELECT 'X' UNION SELECT 'Y' UNION SELECT 'Z'

) AS symbols

ORDER BY ch ASC;

SELECT @zero_digit = symbol FROM @digits WHERE value = 0;

SELECT TOP 1 @highest_digit = symbol FROM @digits ORDER BY value DESC;

-- 2. assume valid input (functions can't throw errors) -----------------------

SET @Ticket = RTRIM(LTRIM(@Ticket));

-- 3. carry the 1 -------------------------------------------------------------

SET @place = LEN(@Ticket);

WHILE @place > 0 AND SUBSTRING(@Ticket, @place, 1) = @highest_digit

BEGIN

SET @Ticket = STUFF(@Ticket, @place, 1, @zero_digit);

SET @place = @place -1;

END

-- 4. final increment ---------------------------------------------------------

IF @place = 0 BEGIN

DECLARE @one_digit CHAR(1), @length INT;

SELECT @one_digit = symbol FROM @digits WHERE value = 1;

SET @length = LEN(@Ticket);

SET @Ticket = @one_digit + @Ticket; -- if this overflows, it will truncate the right most "digit"

IF @length = LEN(@Ticket) -- still the same length only if overflowed...

SET @Ticket = REPLICATE(@zero_digit, @length); -- roll over back to zero

END ELSE BEGIN

DECLARE @next_digit CHAR(1);

SET @next_digit = (

SELECT symbol FROM @digits WHERE value = (

SELECT value +1 FROM @digits WHERE symbol = SUBSTRING(@Ticket, @place, 1)));

SET @Ticket = STUFF(@Ticket, @place, 1, @next_digit);

END

-- 5. Return the new Ticket number

RETURN @Ticket;

END