Incrementing Alpha Numeric field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Incrementing Alpha Numeric field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-07 : 07:41:11

John writes "First off I am a Visual Basic developer who has recently been thrown into a DB developer position(which Im happy about, mind you) but I dont know how to do certain things. So i have a question on how to increment an alpha numeric field. I have written the code in VB and tried to get one of the other DBAs to convert it to a stored proc but no luck. Well anyway, here it goe....I have a field called CustNum char(5)..This field as of right now is just numeric until it hits '99999'. Which will be soon. When it does reach '99999' I must increment to 'A0001' and so on and so forth....If this helps, I will give my VB code and if someone knows how I can convert this I will be extremely grateful..And for all you VB haters...sorry bout this...PLEASE HELP

Public Function GetNextID(CurrID As String) As String
Dim aNum(4) As Integer
Dim aNumCnt As Integer
Dim nNum As Long
Dim nNumMax As Long
Dim ResID As String
Dim i, c

aNumCnt = 0
For i = 1 To 5
c = UCase$(Mid(CurrID, i, 1))
If Asc(c) >= Asc("A") Then
aNum(4 - aNumCnt) = Asc(c) - Asc("A") + 1
aNumCnt = aNumCnt + 1
Else
Exit For
End If
Next i
nNum = Val(Right(CurrID, 5 - aNumCnt))

nNumMax = 10 ^ (5 - aNumCnt) - 1
nNum = nNum + 1
If nNum > nNumMax Then
nNum = 0
If aNumCnt > 0 Then
aNum(5 - aNumCnt) = aNum(5 - aNumCnt) + 1
Else
aNum(4) = 1
End If
For i = 4 To 5 - aNumCnt Step -1
If aNum(i) > 26 Then
aNum(i) = aNum(i) - 26
aNum(i - 1) = aNum(i - 1) + 1
End If
Next i
End If

aNumCnt = 0
For i = 4 To 0 Step -1
If aNum(i) > 0 Then
ResID = ResID & Chr(aNum(i) - 1 + Asc("A"))
aNumCnt = aNumCnt + 1
End If
Next i
ResID = ResID & Format(nNum, String(5 - aNumCnt, "0"))
GetNextID = ResID
End Function"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-07 : 09:27:38

This will only provide limited functionality, but you should be able to take it from here ...


declare @curr char(5)
select @curr = 'A9999'

select
    case when isnumeric(@curr)=1 then
         case when convert(int,@curr)<99999 then
                 reverse(left(reverse('00000'+convert(varchar,convert(int,@curr)+1)),5))
              else
                 'A0001'
         end
         else
         case when convert(int,right(@curr,4))<9999 then 
                 left(@curr,1) + reverse(left(reverse('0000'+convert(varchar,convert(int,right(@curr,4))+1)),4))
              else
                 char(ascii(left(@curr,1))+1) + '0001'
         end
    end

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 09:42:58

quote:

This field as of right now is just numeric until it hits '99999'. Which will be soon. When it does reach '99999' I must increment to 'A0001' and so on and so forth....If this helps

Huh..the field is numeric? Do you mean decimal(4,0)?

Int can go to:

quote:

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

And why (I can't belive I'm doing this) don't you alter the column (it is a column right?) in to an IDENTITY column?

I mean if it's "numeric" already....

Brett

8-)

SELECT POST=NewId()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-07 : 10:32:41

Also, consider just using identities and converting the number to the format you desire.

check out my code at that converts an integer into the range "00000" - "zzzzz" at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27915

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-07 : 14:33:18

I wrote this before I realized Jay had written the same thing (only different).

CREATE FUNCTION dbo.GetNextID(@CurrID As CHAR(5))

RETURNS CHAR(5)
AS
DECLARE @digit4 CHAR(4)
SET  @digit4 = RIGHT(CAST(CAST(RIGHT(@CurrID, 4) AS int) + 1 AS CHAR(5)), 4) -- Inc the integer part

IF @digit4 <> '0000' BEGIN -- It didn't wrap this time

  RETURN(LEFT(@CurrID, 1) + @digit4)

END ELSE BEGIN -- Bump the leading digit '0' to '9' - hop - 'A' to 'Z' and beyond !

  IF LEFT(@CurrID,1) <> '9' BEGIN -- Special case the incriment of '9' to 'A'
     RETURN(CHAR(ASCII(LEFT(@CurrID,1))+1)+ @digit4)
  END ELSE BEGIN
     RETURN('A'+ @digit4)
  END

END

END

I haven't tried this.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-09 : 15:42:09

There's also an article on how do do this.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources