Increment alpha numeric string in stored procedure

have a 5 character field and what I need to do is increment from 00001 - ZZZZZ....So my process would be

(00001 is the first possible value)
00001 - 99999
A0001 - A9999
B0001 - B9999
...
...
...
Z0001 - Z9999
AA001 - AA999
AB001 - AB999
...
...
...
AZ001 - AZ999
BA001 - BA999
...
BZ001 - BZ999
...
...
ZZ001 - ZZ999
AAA01 - AAA99
...
...
ZZZZZ(being the last possible value)


How do I do this in SQL Server stored procedure and not in the .NET code.
Thanks,
dotnetengAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nrbreenConnect With a Mentor Commented:
Yes
 if @cval2=10 select @c2='A'

should be

 if @cval2=58 select @c2='A'
0
 
nrbreenCommented:
What you ask for is NOt an "incrementing" sequence.
You show AA001  coming AFTER  Z0001, and that just doesn't make sense - it wouldn't sort that way...

An incrementing sequence would be

00001-00009-0000A-0000Z
00011-00019-0001A-0001Z
.....
ZZZZ1-ZZZZ9-ZZZZA-ZZZZZ

If you simply must have your original schema, you may need to offer more points to get a solution.
0
 
Kevin CrossChief Technology OfficerCommented:
I think you misunderstood, nrbeen, or maybe I am misreading. I see "have a 5 character field" as meaning, 00001 - 99999 is a range. Then the next range is A0001 - A9999.

'00001'
'AA001'
'Z0001'

Alphanumerical sort, 'Z0000' is after 'AA000'. :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dotnetengAuthor Commented:
You are correct, the sequence should go like this...
00000
00001

0000A
0000B


0000Z
00010

ZZZZZ

I do need to have a stored procedure to achieve this, I already have a VB.NET code.

Thanks,
0
 
Kevin CrossChief Technology OfficerCommented:
I see what you are saying now, nrbreen!
0
 
dotnetengAuthor Commented:
If this helps, but I saw this solution on this site, but it does not take care of the cases where "Z" is between the numbers in the string ( particularly after Z if there is a number ...for example, 000Z0 should goto 000Z1, where as this stored procedure gives an error.)

Any Help is appreciated. Thanks,


CREATE PROCEDURE [dbo].[IncrementCustNum]
      @CustNum VARCHAR(5),
      @NewCustNum VARCHAR(5) OUTPUT
AS
DECLARE @firstChar INT

IF RIGHT(@CustNum, 1) BETWEEN '0' AND '9'
BEGIN
    SET @firstChar = PATINDEX('%[0-9]%', @CustNum)
    IF SUBSTRING(@CustNum, @firstChar, 5) <> LEFT('99999', 6 - @firstChar)
         SET @NewCustNum = LEFT(@CustNum, @firstChar - 1) +
          RIGHT('0000' + CAST(CAST(SUBSTRING(@CustNum, @firstChar, 5) AS INT) + 1 AS VARCHAR(5)), 6 - @firstChar)
    ELSE
         SET @NewCustNum = LEFT(@CustNum, @firstChar - 2) +
          CHAR(ASCII(SUBSTRING(@CustNum, @firstChar - 1, 1)) + 1) +
          RIGHT('0000' + CAST(CAST(SUBSTRING(@CustNum, @firstChar, 5) AS INT) + 1 AS VARCHAR(5)), 6 - @firstChar)
END --IF
ELSE
BEGIN
IF RIGHT(@CustNum, 1) < 'Z'
    SET @NewCustNum = LEFT(@CustNum, LEN(@CustNum) - 1) + CHAR(ASCII(RIGHT(@CustNum, 1)) + 1)
ELSE
      BEGIN
            SET @firstChar = LEN(@CustNum) + 1 - PATINDEX('%[^Z]%', REVERSE(@CustNum))
            IF @firstChar > LEN(@CustNum)  -- @CustNum is all Z's!!
                  SET @NewCustNum = '00000'
            ELSE
                SET @NewCustNum = LEFT(@CustNum, @firstChar - 1) +
                        CHAR(ASCII(SUBSTRING(@CustNum, @firstChar, 1)) + 1) +
                        REPLICATE('0', 5 - @firstChar)
      END --ELSE
END --ELSE

GO
0
 
Kevin CrossChief Technology OfficerCommented:
I am sure this can be done some easier way, but here is what comes to my mind.

-Create a table of valid values, '00000' to 'ZZZZZ', in a single CHAR(5) column. e.g., CustNums
-Index this column.
-In your increment customer number code, simply grab the MIN(CustNum) from your CustNums table WHERE CustNum > @CustNum
0
 
dotnetengAuthor Commented:
Hi mwvisa1, This table will need few million values, so it's not a feasible solution.
0
 
Kevin CrossChief Technology OfficerCommented:
Why NOT? You only have to create the table once and with the index, it will probably perform better than the stored procedure and with much less code.
0
 
nrbreenCommented:
This should give the full range of increments -
extra vars used to simplify debugging.

CREATE PROCEDURE [dbo].[Z_Increment]
      @inputcode VARCHAR(5),
      @result VARCHAR(5) OUTPUT
AS

declare @i as int , @c as char(1), @c2 as char(1), @cval as int, @cval2 as int, @carry as int 

select @result=''
select @carry=1
select @i=5
while @i>0
  begin
    select @c=substring(@inputcode,@i,1)
    select @cval=ascii(@c)   
    
    select @cval2=@cval + @carry
    select @carry=0
    
    select @c2=CHAR(@cval2)
    if @cval2=10 select @c2='A'
    if @cval2 =91 
       begin
       select @c2='0'
       select @carry=1
       end

    select @result=@c2  + @result   -- append to front of result 
    select @i=@i-1
  end
-- select @result 

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
@dotneteng: I guess it is 60 million. *laughing* Should have done the math. :)
0
 
Kevin CrossChief Technology OfficerCommented:
@nrbreen: looks good. If you are using ASCII() value, though, when is the @cval2 going to equal 10? Do you mean 58.
0
 
Kevin CrossChief Technology OfficerCommented:
0-9 is ASCII 48-57, A-Z is 65-90; therefore, the 91 makes sense, just not the 10.
0
 
dotnetengAuthor Commented:
nrbreen,

This is almost correct. It doesn't work with the cases where there is 9 at the end...
00999, 00099...I am stilll testing with some more cases.

Thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
Replace:
if @cval2=10 select @c2='A'

With:
if @cval2=58 select @c2='A'
0
 
Kevin CrossChief Technology OfficerCommented:
And sorry again @dotneteng about the not doing math on number of rows. I have utility tables with million rows like numbers table and performs quite well, but sixty million is another ball game. *smile*
0
All Courses

From novice to tech pro — start learning today.