Auto Increment Alpha Numeric Field in SQL

Question

I was wondering if there is an easy way to do this, instead of my reinventing the wheel here. I am using SSMS. There is a number JKTUTS0 that needs to auto increment. So the next one would be JKTUTS1, JKTUTS2, JKTUTS3... then JKTUTT0

This is what I have so far, but it seems overly complicated and I'm stuck at this point. (It is not the primary key.) Is there an easier way to do this, or can someone please help me out? I'm still new to all this. Thanks.

DECLARE @IssueRecid CHAR(15) = (SELECT MAX(recid) FROM InventoryIssues) 
DECLARE @IssueNumber CHAR(10) = (SELECT IssueNumber FROM InventoryIssues WHERE recid=@IssueRecid)  
-- Returns JKTUTS0  

DECLARE @IssueNumber1 CHAR(1) = (SELECT LEFT (@IssueNumber, 1))  
DECLARE @IssueNumber2 CHAR(1) = (SELECT SUBSTRING (@IssueNumber, 2,1)) 
DECLARE @IssueNumber3 CHAR(1) = (SELECT SUBSTRING(@IssueNumber, 3,1))  
DECLARE @IssueNumber4 CHAR(1) = (SELECT SUBSTRING(@IssueNumber, 4,1)) 
DECLARE @IssueNumber5 CHAR(1) = (SELECT SUBSTRING(@IssueNumber, 5, 1))
DECLARE @IssueNumber6 CHAR(1) = (SELECT SUBSTRING(@IssueNumber, 6, 1)) 
DECLARE @IssueNumber7 CHAR(1) = (SELECT SUBSTRING(@IssueNumber, 7, 1))

IF @IssueNumber7 = 9 
BEGIN 
DECLARE @NewIssueNumber7 CHAR(1) = 0
DECLARE @NewIssueNumber6 CHAR(1) = needs to be the letter after S

Which RDBMS are you using? MySQL? SQL-Server? Please reference it on your question and tag it, as it will attract more users who will be able to help — andrechalom, Apr 04 '16 at 17:52
Don't write auto-incremented columns yourself. Ise identity. That's why it's there. To generate the data you want you van either generate it on select or as a computed column. — Zohar Peled, Apr 04 '16 at 18:09
Are digits 1 thru 6 always alpha, and digit 7 always numeric? Are the alpha digits always A..Z and the numeric digit always 0..9? — James L., Apr 04 '16 at 18:30
The code that James posted appears to do what you want. However, do you notice how absolutely ridiculous that is? The code itself is fine, it is the requirement that is absurd here. Now consider how many combinations of letters you don't want to allow. You can spell a LOT of interesting words with this kind of thing. Add to that the challenge of concurrency and you have a hot mess on your hands. — Sean Lange, Apr 04 '16 at 19:25
Yes, it is. I am just trying to mimic what our off-the-shelf software is doing, while adding in some additional features that it doesn't have. And no, they don't sensor interesting words. — , Apr 05 '16 at 19:45

James L. · Answer 1 · 2016-04-04T19:13:50.870

I'm sure there are several ways to do this, all of which are greatly simplified by having access to the db to create functions and such. Assuming you have no such access, here some SQL that will increment the issue number as you have requested:

declare @IssueNumber char(7) = 'JKTUTS1'

declare @c1 char(1) = substring(@IssueNumber, 1, 1)
       ,@c2 char(1) = substring(@IssueNumber, 2, 1)
       ,@c3 char(1) = substring(@IssueNumber, 3, 1)
       ,@c4 char(1) = substring(@IssueNumber, 4, 1)
       ,@c5 char(1) = substring(@IssueNumber, 5, 1)
       ,@c6 char(1) = substring(@IssueNumber, 6, 1)
       ,@c7 char(1) = substring(@IssueNumber, 7, 1)
       ,@rtn varchar(8) = null

if @c7 <> '9'
begin
  set @c7 = char(ascii(@c7) + 1)
end
else if @c6 <> 'Z'
begin
  set @c7 = '0'
  set @c6 = char(ascii(@c6) + 1)
end
else if @c5 <> 'Z'
begin
  set @c7 = '0'
  set @c6 = 'A'
  set @c5 = char(ascii(@c5) + 1)
end
else if @c4 <> 'Z'
begin
  set @c7 = '0'
  set @c6 = 'A'
  set @c5 = 'A'
  set @c4 = char(ascii(@c4) + 1)
end
else if @c3 <> 'Z'
begin
  set @c7 = '0'
  set @c6 = 'A'
  set @c5 = 'A'
  set @c4 = 'A'
  set @c3 = char(ascii(@c3) + 1)
end
else if @c2 <> 'Z'
begin
  set @c7 = '0'
  set @c6 = 'A'
  set @c5 = 'A'
  set @c4 = 'A'
  set @c3 = 'A'
  set @c2 = char(ascii(@c2) + 1)
end
else if @c1 <> 'Z'
begin
  set @c7 = '0'
  set @c6 = 'A'
  set @c5 = 'A'
  set @c4 = 'A'
  set @c3 = 'A'
  set @c2 = 'A'
  set @c1 = char(ascii(@c1) + 1)
end
else
  set @rtn = 'OVERFLOW'

set @rtn = coalesce(@rtn, @c1 + @c2 + @c3 + @c4 + @c5 + @c6 + @c7)

select @rtn as NewIssueNumber

Thank you so much. I really appreciate it. – Apr 04 '16 at 18:56 — , Apr 04 '16 at 18:56

score 0 · Answer 2 · answered Apr 04 '16 at 19:30

Here's a slightly more generic solution:

DECLARE @PreviousIssueNumber char(7),
        @NewIssueNumber char(10);

SET @PreviousIssueNumber = 'JKTUTT9';

DECLARE @LastCharacter char(1);
SET @LastCharacter = RIGHT(@PreviousIssueNumber, 1);

IF @LastCharacter != '9'
BEGIN
    SET @LastCharacter = CHAR(ASCII(@LastCharacter) + 1);
    SET @NewIssueNumber = LEFT(@PreviousIssueNumber, 6) + @LastCharacter;
END
ELSE
BEGIN
    SET @LastCharacter = '0';

    DECLARE @StringIndex int;
    SET @StringIndex = 6;

    DECLARE @CharacterAtStringIndex CHAR;
    SET @CharacterAtStringIndex = SUBSTRING(@PreviousIssueNumber, @StringIndex, 1);

    WHILE @CharacterAtStringIndex = 'Z'
    BEGIN
        SET @StringIndex = @StringIndex - 1;
        SET @CharacterAtStringIndex = SUBSTRING(@PreviousIssueNumber, @StringIndex, 1);
    END

    SET @CharacterAtStringIndex = CHAR(ASCII(@CharacterAtStringIndex) + 1);
    SET @NewIssueNumber = LEFT(STUFF(@PreviousIssueNumber, @StringIndex, 6 - @StringIndex, 'AAAAAA'), 6) + '0';
    SET @NewIssueNumber = STUFF(@NewIssueNumber, @StringIndex, 1, @CharacterAtStringIndex);
END

SELECT @NewIssueNumber;

Auto Increment Alpha Numeric Field in SQL

2 Answers2