kiran.vaichalkar (6/25/2013)
Hi Folks,Thanks again to every1 looking into this for me.
I was in need to generate Alphanumeric ID ( An Identity which is based on PRIMARY KEY [assigned to every row for an user information] AND User Status [Suppose Status can have values {A,B,C,D,E}]
So the Alphanumeric ID for First user could be '1A', Similarly second user '2A', Third '3B', Fourth '4A'.. and some nth user has 'nE'.
So now,
I've found the logic to Derive this Alphanumeric ID.
Of course, It cannot be a Primary KEY so my assumption about it was false tough it can be a UNIQUE KEY.
The logic is as below,
UPDATE EMP_DETAILS SET Use_ID = CAST((EMP_STATUS+''+CAST(ID as varchar)) as varchar)
Where
EMP_DETAILS: Table for User information.
ID: Primary Key & EMP_STATUS[As the user chooses from Front end]
But finally, what i got is I have to re-look to the written records and re-process for the USE_ID(the required Alpha numeric ID).
So my question is, Is there any other way like to achieve this, alike the derived columns are(Value get calculated and store automatically)?
So what happens when you get to 26Z? What would 27 be? Perhaps 27AA, 28AB, etc?
I'm trying to be practical here. Really, with enough ingenuity we can do just about anything we want in SQL. I don't question as much as others as to WHY people have unusual requirements. Sometimes we just do because a client or some other power-that-be has made the request and either won't change or it's just not worth the bother.
So in that vein of thought, you CAN do this but not with an identity column which is of course always and only numeric. What you'll probably need to do is create a computed column to hold the alphanumeric value. The function/formula for that computed column will then have to get the identity value and using some algorithm determine the letter combination that would be next in sequence. To get alpha characters from a number you can use the CHAR operator.
For example, you can easily convert the number 67 into its ASCII equivalent,
which in this case is an upper-case 'C', and back again.
SELECT CHAR(67) -- returns 'C'
--this does the opposite
SELECT ASCII('C') -- returns '67'
So with that, you could do your string manipulations. But it will get complicated once you get past that first 'Z'! I've been trying to do something like that for awhile as an experiment and it never seems to work correctly and then the code turns into spaghetti.
Probably the best method would be a table of characters similar to a tally table:
ID INT IDENTITY(1,!)
VAL VARCHAR(5)
The values would be like this or whatever pattern you choose:
1 A
2 B
...
26 Z
27 AA
28 AB
...
53 AZ
54 AAA
55 AAB
...
80 AAAA
81 AAAB
Then you do a JOIN on a tally table with the IDENTITY value to concatenate your artificial key into a single string.