Sequence creation

Question

Post reply

Sequence creation

SQLServerMS

SSC Veteran

Points: 223
More actions
September 7, 2011 at 5:22 am

#242849

Hi,
I need to populate the values for a column in a sequential manner as below, these need to be auto generated:
i1st it starts with
000001
000002
......
999999
A00001
A00002
......
A99999
B00001
......
B99999
......
Z99999
ZA0001
......
ZA9999
......
ZZZZZZ
How can i achieve this.... please help me
Have a nice day!

Viewing 15 posts - 1 through 15 (of 29 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's not easy, and it still requires an identity() column to do it;

I answered a similar post recently here:

http://www.sqlservercentral.com/Forums/Topic1162428-1292-1.aspx#bm1162458

check out that post and see if the example there is what you can use; it's not exactly the format you wanted, but it's basically doing what you asked, and the code can serve as a model.

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!

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

It's not easy and has hidden pitfalls.

I strongly suggest to take a look at this great article by Paul White.

http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx

Hope this helps

Gianluca

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

SQLServerMS (9/7/2011)
Hi,
I need to populate the values for a column in a sequential manner as below, these need to be auto generated:
i1st it starts with
000001
000002
......
999999
A00001
A00002
......
A99999
B00001
......
B99999
......
Z99999
ZA0001
......
ZA9999
......
ZZZZZZ
How can i achieve this.... please help me

Just imagine all of the nasty words that can be spelled with 3 to 6 letters. Nice way to get sued. Don't do this! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 4

I'd take Lowell's approach, though if the logic for converting the value in the identity column to the alpha-numeric value you want is non-trivial, you can easily abstract it to a function, then define the computed column using the function. Be sure to specify the computed column as "persisted" as well.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 5

Does the sequence not have any zero's in the rightmost position? for example, is 000010 in your sequence or is that skipped, like A00000?

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

notoriousdba (9/7/2011)
I'd take Lowell's approach, though if the logic for converting the value in the identity column to the alpha-numeric value you want is non-trivial, you can easily abstract it to a function, then define the computed column using the function. Be sure to specify the computed column as "persisted" as well.

Nope. I wouldn't take anyone's approach on this. I've seen 2 Grandmother's sue because of alpha-numeric serial numbers that spelled out swear words.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLServerMS SSC Veteran Points: 223 More actions · Answer 7

Does the sequence not have any zero's in the rightmost position? for example, is 000010 in your sequence or is that skipped, like A00000?

--------------------------------------------------------------------------------

the sequence has 000010.....999999

Have a nice day!

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 8

Jeff Moden (9/7/2011)
notoriousdba (9/7/2011)
I'd take Lowell's approach, though if the logic for converting the value in the identity column to the alpha-numeric value you want is non-trivial, you can easily abstract it to a function, then define the computed column using the function. Be sure to specify the computed column as "persisted" as well.
Nope. I wouldn't take anyone's approach on this. I've seen 2 Grandmother's sue because of alpha-numeric serial numbers that spelled out swear words.

References, or it didn't happen.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 9

SQLServerMS (9/7/2011)
the sequence has 000010.....999999

Then the example is wrong: In your example A00000 is missing in between 999999 and A000001 and this pattern seems to be repeated in later blocks.

The correct sequence would be:

000001

000002

......

999999

A00000

A00001

A00002

......

A99999

B00000

B00001

......

B99999

......

Z99999

ZA0000

ZA0001

......

ZA9999

......

ZZZZZZ

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

notoriousdba (9/8/2011)
Jeff Moden (9/7/2011)
notoriousdba (9/7/2011)
I'd take Lowell's approach, though if the logic for converting the value in the identity column to the alpha-numeric value you want is non-trivial, you can easily abstract it to a function, then define the computed column using the function. Be sure to specify the computed column as "persisted" as well.
Nope. I wouldn't take anyone's approach on this. I've seen 2 Grandmother's sue because of alpha-numeric serial numbers that spelled out swear words.
References, or it didn't happen.

I AM the reference! 😉 Do it your way and embarass your company and possibly get sued for offending someone's sensibilities. I can't wait to see what happens when you send someone a document with a serial number of A__HOLES or F__KHEAD on it. And I don't like your inference. Are you calling me a liar?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

Jeff Moden (9/8/2011)
I AM the reference!

Notorious, don't know about you, this is more than enough for me! 😉

-- Gianluca Sartori

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 12

Gianluca Sartori (9/8/2011)
Jeff Moden (9/8/2011)
I AM the reference!
Notorious, don't know about you, this is more than enough for me! 😉

LOL!

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 13

Jeff Moden (9/8/2011)
notoriousdba (9/8/2011)
References, or it didn't happen.
I AM the reference! 😉 Do it your way and embarass your company and possibly get sued for offending someone's sensibilities. I can't wait to see what happens when you send someone a document with a serial number of A__HOLES or F__KHEAD on it. And I don't like your inference. Are you calling me a liar?

I, as the speaker, imply. You, as the listener, infer. And you infer incorrectly. I'm not calling you a liar, I just don't believe you without proof. It sounds apocryphal to me.

And BTW, it's not MY way, it's the OP's way. I expressed no opinion about what the OP wants to do, though I don't happen to like it very much. It strikes me as being fussy, and overly complicated, but I don't know what his requirements and constraints are.

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 14

I've got it! Exclude vowels from the sequences you generate. That way the only people you might offend are the Welsh.