Generate UNIQUE VARCHAR Identifier?

Question

Post reply

Generate UNIQUE VARCHAR Identifier?

_UB

SSCrazy

Points: 2987
More actions
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

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

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

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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:

-- 1ABCFLABC_FL_00001Smith

-- 2DEFNYDEF_NY_00002Jones

[/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!

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

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)

_UB SSCrazy Points: 2987 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

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)

_UB SSCrazy Points: 2987 More actions · Answer 6

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