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.
| Author |
Topic |
|
mikkyvikky
Starting Member
1 Post |
 Posted - 2005-02-02 : 21:39:39
|
I need to generate an Alphanumeric non duplication sequence in a MSSQL 7.0 Stored Procedure it goes this way.Table:Create table test(id int Not Null, custcode varchar(10) Not Null, custname varchar(75))The id will hold a running sequence which i plan to use an Identity. While in the Custcode i need to use a Alphabetic sequence.First A then B,C,D,E, ......Z once done next sequence will have to follow AA,AB,AC,AD..... AZ once this is done next BB,BC,BD....BZ This way.Kindly help me in this i would be very thankfull.RegardsMikkymouse  |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-02 : 21:47:03
|
| Try this:http://www.sqlteam.com/item.asp?ItemID=1417 |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-02 : 22:08:47
|
| One way is to use an identity column, but don't use that as your PK -- just convert it into a string of letters, using some good old fashioned math/CS skills:-- a sample table:create table #Tmp(i int identity(0,1), Code varchar(10))-- generate sample data:insert into #Tmp (Code)select top 1000 Nullfrom syscolumns a cross join syscolumns b-- and using a formula like below, this is one way to create a sequence of letters from a number:select char(65+ (i/26/26/26)%26) + char(65+ (i/26/26)%26) + char(65 + (i/26)%26) + char(65 + i%26) from #tmpgodrop table #tmp- Jeff |
|
|
|
|
|
|
|
|
Generate Non Duplicating Sequencial Alphabet in SP