Hello,
I'm trying to code a user defined function under SQL Server 2005 that will increase integer part of alphanumeric value by one. For example, uf_AlphanumericIncrease ('A000299')
should return 'A000300'. Here's what I've done so far;
ALTER FUNCTION uf_AlphaNumericIncrement
(
@ID varchar(10)
)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @RES varchar(10);
IF SUBSTRING(@ID,LEN(@ID),1)='9'
SET @RES=SUBSTRING(@ID,1,LEN(@ID)-2)+CAST (CAST(SUBSTRING(@ID,LEN(@ID)-1,1) AS smallint)+1 AS VARCHAR(10))+'0';
ELSE
SET @RES=SUBSTRING(@ID,1,LEN(@ID)-1)+CAST (CAST(SUBSTRING(@ID,LEN(@ID),1) AS smallint)+1 AS VARCHAR(10));
RETURN @RES;
END
But as you can see it only works for last digit. I need to get it under loop so it can work for A002999 and so on. Any ideas?
Edit: Given value might have alpha prefix longer than one character, or none at all.