newby wants to create a sequencial Alpha numeric value

Question

newby wants to create a sequencial Alpha numeric value

johnwh-1128731

SSC Journeyman

Points: 76
More actions
August 19, 2011 at 6:53 am

#390224

I would like to know if anyone has experience creating an auto alphanumeric row identifier. I am working on a database that is quite large, and I would like to have a consistent size identifier that will be used as a barcode scan.
The idea we have come up with is an alpha numeric string 6 characters long starting at 100000 where each position is occupied by a number or letter. The progress of this value should be as follows
100000
100001
...
100009
10000A
...
10000Z
100010
etc.
I have some ideas that generate a random number, but do not want to run a check to insure the number is unique, and I would prefer the values to be generated in sequence.
Thanks for and help or thoughts.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can do it, but you still need an identity() column in your table.

here's an example, see how because of the assumed format with a max length of 5, you are limited to values less than 675999 in this specific examaple.

You'd have to tweak it and expand it to match your specific style:

DROP TABLE X

CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)), --limit=26 * 26 + 999 + 1

XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter

+CHAR((XID/1000)%26+65) --2nd Letter

+REPLACE(STR(XID%1000,3),' ','0'), --The 3 digit numeric part

SOMEOTHERCOL VARCHAR(30)

)

DROP TABLE X

INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')

SET IDENTITY_INSERT X ON

INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')

SET IDENTITY_INSERT X OFF

SET IDENTITY_INSERT X ON

INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')

SET IDENTITY_INSERT X OFF

SELECT * FROM X

/*

XID XCALCULATED SOMEOTHERCOL

1 AA001 WHATEVER

675999 ZZ999 MORESTUFF

*/

--three char table: bigger range

CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 196040000)),

XCALCULATED AS

CHAR((XID/260000)%26+65) --1st Letter

+ CHAR((XID/26000)%26+65) --2nd Letter

+ CHAR((XID/1000)%26+65) --3rd Letter

+ REPLACE(STR(XID%10000,4),' ','0'), --The 4 digit numeric part

SOMEOTHERCOL VARCHAR(30)

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnwh-1128731 SSC Journeyman Points: 76 More actions · Answer 2

I actually think this might work!!

I also found a sample of a function that takes a string and increments the alpha numeric string by 1.

a trigger on the table calls the function and then inserts the new record.

http://www.sqlservercentral.com/scripts/Miscellaneous/31448/

It seems that the more i search, the more I learn

Thanks for your help:-):-)

Lowell SSC Guru Points: 323518 More actions · Answer 3

glad that my example got you thinking!

that one's from an old post where someone wanted ranges from like AA001,AA002,AA003 thru ZZ999

the table under that i tested for AAA0001 thru ZZZ9999, in case ~600K was not enough rows.

What are you using this for? although i found it really neat to create/code this, i never found a practical use for it....maybe aribtrary part numbers or something?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnwh-1128731 SSC Journeyman Points: 76 More actions · Answer 4

the idea is to create a unique identifier for a scancode. we want to keep the value short, (less tha 5 characters) but have a large array of values, (more than 1,000,000)

By using an alpha/numeric you have in effect 36 ^ 5 unique values to work with.

Thanks again for your help if did in fact help solve the issue.