Post reply

Sequence creation

  • 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!

  • September 7, 2011 at 5:43 am

    #1377896

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

    I answered a similar post recently here:

    http://qa.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!

  • September 7, 2011 at 5:57 am

    #1377906

    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

  • September 7, 2011 at 6:12 am

    #1377914

    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)
    Intro to Tally Tables and Functions

  • September 7, 2011 at 11:44 am

    #1378185

    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.

  • September 7, 2011 at 2:46 pm

    #1378285

    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?

  • September 7, 2011 at 6:46 pm

    #1378319

    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)
    Intro to Tally Tables and Functions

  • September 7, 2011 at 10:53 pm

    #1378360

    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!

  • September 8, 2011 at 6:44 am

    #1378493

    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.

  • September 8, 2011 at 6:56 am

    #1378505

    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?

  • September 8, 2011 at 10:15 am

    #1378717

    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)
    Intro to Tally Tables and Functions

  • September 8, 2011 at 10:27 am

    #1378733

    Jeff Moden (9/8/2011)

    I AM the reference!

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

    -- Gianluca Sartori

  • September 8, 2011 at 10:38 am

    #1378752

    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!

  • September 8, 2011 at 11:08 am

    #1378780

    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.

  • September 8, 2011 at 11:13 am

    #1378784

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

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

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