Post reply

Generate UNIQUE VARCHAR Identifier?

  • February 27, 2009 at 2:45 pm

    #200307

    Guys,

    We need to generate a unique number, LIKE IDENTITY, that is Alphanumeric for our table. The table already has IDENTITY column.

    This unique column would be like:

    ABC is the category

    FL is the state Florida

    00001 is the number, this number is not the same as the IDENTITY column value

    IDENTITY Column Varchar Identity Column Name

    1 ABC_FL_00001 Smith

    2 ABC_FL_00003 John

    3 ABC_FL_00004 jake

    4 ...

    5 ...

    6 DEF_FL_23928 Jim

    7 DEF_FL_23929 Rob

    8 DEF_FL_23930 Tim

    9 ...

    10

    11 DEF_GA_00010 Tin <-- for Georgia

    12 DEF_CA_00574 <-- for California

    13 ...

    14 ...

    The number part could repeat, but the entire column value must be unique

    Please share any ideas.

    thanks,

    _Uday

    SqlTimes Blog on Sql Server

  • February 27, 2009 at 3:27 pm

    #951412

    Could you add a field as a varchar and use the computed Column Specification "Formula" to design a unique key.

  • February 27, 2009 at 5:28 pm

    #951441

    you are on the right track as using an identity to help generate the varchar; that's the way we've suggested it for similar issues.

    like JKSQL said, the way to do this is with a calculated field. you also want to make it PERSISTED in the table, in case you want it to be a FK from a different table.

    I'm sure you are aware that your pattern maked it look like you will never expect any more rows than 99999; the code below will simply break if you exceed that number, so plan the preceeding zeros accordingly.

    here's an example:

    [font="Courier New"]CREATE TABLE Whatever(

    WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    CategoryCode VARCHAR(3),

    StateCode    CHAR(2),

    myUniqueCalculatedVarchar AS CategoryCode + '_' + StateCode + '_' + RIGHT('000000' + CONVERT(VARCHAR,WhateverID),5 ) PERSISTED UNIQUE,

    LastName   VARCHAR(30))

    INSERT INTO whatever(categorycode,statecode,lastname)

    SELECT 'ABC','FL','Smith' UNION ALL

    SELECT 'DEF','NY','Jones'

    SELECT * FROM Whatever

    --Results:

    -- 1 ABC FL ABC_FL_00001 Smith

    -- 2 DEF NY DEF_NY_00002 Jones

    [/font]

    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!

  • February 27, 2009 at 7:51 pm

    #951467

    That works just fine, but there's only 100,000 numbers available that way. This type of serialization is really bad just because of that... even if the numeric sequence is allowed to repeat. But...

    One way to get the repeating numbers is to have a table for each location in the form of a "partitioned table"... that way, each location can have it's own IDENTITY column and nothing special has to be done for inserts and updates. Then use Lowell's fine calculated column achieve the correct alpha-numeric serial number.

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

  • March 9, 2009 at 1:47 pm

    #956371

    Like I pointed out in the example, the value in ID column doesn't necessarily have t match the integer value in the 'VARCHAR ID' column

    SqlTimes Blog on Sql Server

  • March 9, 2009 at 6:14 pm

    #956498

    UB (3/9/2009)

    Like I pointed out in the example, the value in ID column doesn't necessarily have t match the integer value in the 'VARCHAR ID' column

    So, after 2 weeks, have you tried any of the above?

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

  • March 10, 2009 at 10:39 am

    #956939

    Sorry about the delay in reporting back ...

    I tried the idea proposed by Lowell, on the same day. I had to try a modified version as my table structure is slightly different. (State and Category are not part of the same table)

    And I've been trying to get something like this to work

    CREATE TABLE SomeTable(

    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    VarcharID AS 'ABC' + IDENTITY(797879,1) PERSISTED UNIQUE, -- numbering to start at 797879, instead of 1

    LastName VARCHAR(30)

    )

    with the intention of something like

    ID VarcharID

    1 ABC_797879

    2 ABC_797880

    ..

    ..

    ..

    But since that doesnt work, I had to use something like this

    CREATE TABLE SomeTable(

    ID INT IDENTITY(1,1) NOT NULL,

    VarcharID AS 'ABC_' + CONVERT(VARCHAR, ID + 797878) PERSISTED UNIQUE -- numbering to start at 797879, instead of 1

    )

    Hope this makes some sense....

    SqlTimes Blog on Sql Server

Viewing 7 posts - 1 through 6 (of 6 total)

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