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