Create below Function
CREATE FUNCTION dbo.fn_USER_IncrementAlphaNumericString
(
@STRING VARCHAR(255)
)
RETURNS varchar(255) AS
BEGIN
DECLARE @REV_STRING VARCHAR(255),
@POSITION INT,
@CURR_CHAR CHAR(1),
@CURR_CHAR_TYPE VARCHAR(20),
@NEW_CHAR CHAR(1),
@PREV_CHAR CHAR(1),
@PREV_CHAR_TYPE VARCHAR(20),
@NEW_STRING VARCHAR(255),
@INCR_CHAR BIT,
@DEBUG VARCHAR(4000),
@CR CHAR(1)
SET @INCR_CHAR = 1
SET @POSITION = 1
SET @REV_STRING = REVERSE(@STRING)
SET @NEW_STRING = ''
SET @DEBUG = ''
SET @CR = CHAR(10)
SET @DEBUG = @DEBUG + '---- STRING: ' + @STRING + ' ----' + @CR + @CR
WHILE @POSITION <= LEN(@STRING) BEGIN
SET @DEBUG = @DEBUG + '---- POSITION: ' + CAST(@POSITION AS VARCHAR(50)) + ' --(' + CAST(@INCR_CHAR AS CHAR(1)) + ')--' + @CR
SET @CURR_CHAR = SUBSTRING(@REV_STRING, @POSITION, 1)
SET @NEW_CHAR = @CURR_CHAR
IF @CURR_CHAR LIKE '[0-9]' SET @CURR_CHAR_TYPE = 'NUMERIC'
ELSE IF @CURR_CHAR LIKE '[A-Z]' AND ASCII(@CURR_CHAR) >= 97 AND ASCII(@CURR_CHAR) <= 122 SET @CURR_CHAR_TYPE = 'LOWER ALPHA'
ELSE IF @CURR_CHAR LIKE '[A-Z]' AND ASCII(@CURR_CHAR) >= 65 AND ASCII(@CURR_CHAR) <= 90 SET @CURR_CHAR_TYPE = 'UPPER ALPHA'
ELSE SET @CURR_CHAR_TYPE = 'SYMBOL'
SET @DEBUG = @DEBUG + 'CURRENT_CHAR: ' + @CURR_CHAR + @CR
SET @DEBUG = @DEBUG + 'CURRENT_CHAR_TYPE: ' + @CURR_CHAR_TYPE + @CR
IF @INCR_CHAR = 1 BEGIN
IF @CURR_CHAR_TYPE = 'NUMERIC' BEGIN
IF @CURR_CHAR = '9' BEGIN
SET @NEW_CHAR = '0'
END ELSE BEGIN
SET @NEW_CHAR = CAST(CAST(@CURR_CHAR AS INT) + 1 AS CHAR(1))
SET @INCR_CHAR = 0
END
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END
ELSE IF @CURR_CHAR_TYPE = 'LOWER ALPHA' BEGIN
IF @CURR_CHAR = 'z' BEGIN
SET @NEW_CHAR = 'a'
END ELSE BEGIN
SET @NEW_CHAR = CHAR(ASCII(@CURR_CHAR) + 1)
SET @INCR_CHAR = 0
END
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END
ELSE IF @CURR_CHAR_TYPE = 'UPPER ALPHA' BEGIN
IF @CURR_CHAR = 'Z' BEGIN
SET @NEW_CHAR = 'A'
END ELSE BEGIN
SET @NEW_CHAR = CHAR(ASCII(@CURR_CHAR) + 1)
SET @INCR_CHAR = 0
END
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END
ELSE BEGIN
SET @NEW_STRING = @CURR_CHAR + @NEW_STRING
END
END ELSE BEGIN
SET @NEW_STRING = @CURR_CHAR + @NEW_STRING
END
SET @DEBUG = @DEBUG + 'NEW_CHAR: ' + @NEW_CHAR + @CR
SET @DEBUG = @DEBUG + 'NEW_STRING: ' + @NEW_STRING + @CR
SET @DEBUG = @DEBUG + 'POST_INCR INCR_CHAR: ' + CAST(@INCR_CHAR AS CHAR(1)) + @CR
IF @POSITION = LEN(@STRING) AND @INCR_CHAR = 1 BEGIN
IF @CURR_CHAR_TYPE = 'NUMERIC' SET @NEW_STRING = '1' + @NEW_STRING
ELSE IF @CURR_CHAR_TYPE = 'LOWER ALPHA' SET @NEW_STRING = 'a' + @NEW_STRING
ELSE IF @CURR_CHAR_TYPE = 'UPPER ALPHA' SET @NEW_STRING = 'A' + @NEW_STRING
ELSE SET @NEW_STRING = 'a' + @NEW_STRING
END
SET @DEBUG = @DEBUG + 'POST_ADD NEW_STRING: ' + @NEW_STRING + @CR
SET @PREV_CHAR = @CURR_CHAR
SET @PREV_CHAR_TYPE = @CURR_CHAR_TYPE
SET @POSITION = @POSITION + 1
END
RETURN @NEW_STRING
END
GO
Sample Code
select dbo.fn_USER_IncrementAlphaNumericString('P4UPMA00075341')
Output is
P4UPMA00075342