Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Increment alpha numeric string in stored procedure

Posted on 2011-09-23
16
Medium Priority
?
1,470 Views
Last Modified: 2012-05-12
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,
0
Comment
Question by:dotneteng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
16 Comments
 
LVL 3

Expert Comment

by:nrbreen
ID: 36590427
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590467
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
 

Author Comment

by:dotneteng
ID: 36590472
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590481
I see what you are saying now, nrbreen!
0
 

Author Comment

by:dotneteng
ID: 36590727
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590783
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
 

Author Comment

by:dotneteng
ID: 36590808
Hi mwvisa1, This table will need few million values, so it's not a feasible solution.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590835
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
 
LVL 3

Expert Comment

by:nrbreen
ID: 36590844
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590857
@dotneteng: I guess it is 60 million. *laughing* Should have done the math. :)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590863
@nrbreen: looks good. If you are using ASCII() value, though, when is the @cval2 going to equal 10? Do you mean 58.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590870
0-9 is ASCII 48-57, A-Z is 65-90; therefore, the 91 makes sense, just not the 10.
0
 

Author Comment

by:dotneteng
ID: 36590873
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590876
Replace:
if @cval2=10 select @c2='A'

With:
if @cval2=58 select @c2='A'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590880
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
 
LVL 3

Accepted Solution

by:
nrbreen earned 800 total points
ID: 36591096
Yes
 if @cval2=10 select @c2='A'

should be

 if @cval2=58 select @c2='A'
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question