dotneteng
asked on
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,
(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,
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'. :)
'00001'
'AA001'
'Z0001'
Alphanumerical sort, 'Z0000' is after 'AA000'. :)
ASKER
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,
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,
I see what you are saying now, nrbreen!
ASKER
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(@CustN um, @firstChar, 5) AS INT) + 1 AS VARCHAR(5)), 6 - @firstChar)
ELSE
SET @NewCustNum = LEFT(@CustNum, @firstChar - 2) +
CHAR(ASCII(SUBSTRING(@Cust Num, @firstChar - 1, 1)) + 1) +
RIGHT('0000' + CAST(CAST(SUBSTRING(@CustN um, @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(@Cust Num, @firstChar, 1)) + 1) +
REPLICATE('0', 5 - @firstChar)
END --ELSE
END --ELSE
GO
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(@CustN
ELSE
SET @NewCustNum = LEFT(@CustNum, @firstChar - 2) +
CHAR(ASCII(SUBSTRING(@Cust
RIGHT('0000' + CAST(CAST(SUBSTRING(@CustN
END --IF
ELSE
BEGIN
IF RIGHT(@CustNum, 1) < 'Z'
SET @NewCustNum = LEFT(@CustNum, LEN(@CustNum) - 1) + CHAR(ASCII(RIGHT(@CustNum,
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(@Cust
REPLICATE('0', 5 - @firstChar)
END --ELSE
END --ELSE
GO
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
-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
ASKER
Hi mwvisa1, This table will need few million values, so it's not a feasible solution.
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.
This should give the full range of increments -
extra vars used to simplify debugging.
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
@dotneteng: I guess it is 60 million. *laughing* Should have done the math. :)
@nrbreen: looks good. If you are using ASCII() value, though, when is the @cval2 going to equal 10? Do you mean 58.
0-9 is ASCII 48-57, A-Z is 65-90; therefore, the 91 makes sense, just not the 10.
ASKER
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!
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!
Replace:
if @cval2=10 select @c2='A'
With:
if @cval2=58 select @c2='A'
if @cval2=10 select @c2='A'
With:
if @cval2=58 select @c2='A'
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*
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.