Increasing Alphanumeric value in user defined function

Question

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.

I don't have any solution for you, but am curious, and it may help those answering your question, what you would like A99 to increment to. (A100 is the obvious answer, but can you deal with the extra digit?) — Patrick McDonald, Jun 12 '09 at 14:58
Extra digit is alright when limit is reached. In that case, A100 is what my GUI interface does - I need this under UDF due I'm coding a trigger that'll append data. — curious slab, Jun 12 '09 at 15:01
Is the alpha portion always one character or does the function have to handle more (eg. 'AB001' => 'AB002')? — Joe, Jun 12 '09 at 15:12
joe, it has to handle more. Sometimes it might not have prefix at all. — curious slab, Jun 12 '09 at 15:14

gbn · Accepted Answer · 2009-06-12T15:24:03.883

4

Now works with any length of prefix and number part (well upto 20 each)

DECLARE @prefix varchar(20), @numberstr varchar(20), @number int, @Val varchar(40)

SELECT @Val = 'ABCD000006'
--SELECT @Val = 'A03'

SELECT @prefix = LEFT(@Val, PATINDEX ('%[0-9]%', @Val) -1)
SELECT @numberstr = SUBSTRING(@Val, PATINDEX ('%[0-9]%', @Val), 8000)
SELECT @number = CAST(@numberstr AS int) + 1
SELECT @prefix + RIGHT(REPLACE(SPACE(LEN(@numberstr)), ' ', '0') + CAST(@number AS varchar(20)), LEN(@numberstr))

edited Jun 12 '09 at 15:24

answered Jun 12 '09 at 15:10

gbn

422,506
82
585
676

Length of prefix may vary. Like ARD0001. How'd you determine prefix' length? – curious slab Jun 12 '09 at 15:13
Works great, I had changed static `6` to LEN(@Val)-LEN(@prefix) before NewWal at your previous solution, but this works too. Thank you very much. – curious slab Jun 12 '09 at 15:29

Matt · Answer 2 · 2009-06-12T15:44:56.687

Assuming that the alpha part of your alphanumeric is always only the first character, this should work.

EDIT: OK, if the alpha part varies in length this gets ugly pretty quickly for a UDF. This is just a quick-and-dirty solution, so it can probably be optimized a bit, but the logic should be sound.

EDIT AGAIN: patindex() ftw - I learned something new today ;-)

ALTER FUNCTION uf_AlphaNumericIncrement
(
@ID varchar(10)
)
RETURNS VARCHAR(10) AS
BEGIN
    DECLARE @RES varchar(10);
    DECLARE @num int;
    DECLARE @prefix varchar(10);
    set @prefix = left(@id, patindex('%[0-9]%', @id) -1)
    set @num = cast(right(@id, len(@id) - len(@prefix)) as int) + 1
    set @res = @prefix + replicate('0', len(@id) - len(@prefix) - len(@num)) + cast(@num as varchar(10))

    RETURN @RES;
END

I should have made question clear about alpha part at the first place, so I have edited it now. — curious slab, Jun 12 '09 at 15:17

Joe · Answer 3 · 2009-06-12T15:35:42.093

CREATE FUNCTION dbo.uf_ANinc
(
  @in varchar(10)
)
RETURNS varchar(10) AS
BEGIN
    DECLARE @prefix varchar(10);
    DECLARE @res varchar(10);
    DECLARE @pad varchar(10);
    DECLARE @num int;
    DECLARE @start int;

    SET @start = PATINDEX('%[0-9]%',@in);
    SET @prefix = LEFT(@in, @start - 1 );
    SET @num = CAST(  RIGHT( @in, LEN(@in) - @start ) AS int  ) + 1
    SET @pad = REPLICATE( '0', 10 - LEN(@prefix) - CEILING(LOG(@num)/LOG(10)) );
    SET @res = @prefix + @pad + CAST( @num AS varchar);

    RETURN @res
END
GO

SELECT dbo.uf_ANinc('ABC000123');

Increasing Alphanumeric value in user defined function

3 Answers3

Linked