Click here to Skip to main content
15,896,063 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to create an alpha numeric autogenerated column into sql server table and make this column primary key.
Please Help me .
Posted
Comments
George Jonsson 30-Nov-15 1:45am    
Are there any rules for the alpha numeric string?
Length of the string. Maybe fixed length.
Base of the string? For example base34 => 0-9 + A-Z, but I and O excluded.
nitinkumar06 30-Nov-15 1:51am    
not there is no rule for alpha numeric . i want to create a Field for my application which contain raised Ticket No which will be autogenerated and should be primary key and i can fix any charcter at Starting position for example ( my Sequence should be TK-No-001,TK-NO-002,TK-NO-003 SO I want to fix TK-NO- and next two or three digits will incresing accroding to no of records inserted .
George Jonsson 30-Nov-15 3:03am    
Why not add that information from the beginning?
And when responding to a comment, please use the reply button otherwise the person you address will not get notified.

1 solution

You can use CHAR or a VARCHAR as the column type, depending on if you want to have a fixed length or not.
Then just set it as the IDENTITY column.

For the auto-generation part, you have to do that your self.

You can do it either in a stored procedure or in a trigger that is fired before the row is inserted.
Either way you need to have a way to correctly increment the sequence number part of the string.
Basically what you are saying is that you want to have a key that consists of a prefix, such as TK-NO-, and a unique sequence number part with 3 digits, such as 000-999.
This design gives you only a 1000 unique numbers before you run out of keys, so maybe you need more digits or a way to change the prefix.

Another small issue is to rely on the number of rows inserted. What if you later on decide that you are going to allow rows to be deleted from the table?
Then you will end up with duplicates for sure.
A better way is to get the last inserted row, extract the numeric part of the primary key and increment by 1.
SQL
CREATE PROCEDURE AddRow(IN _someData VARCHAR)
BEGIN
    SET @prefix = 'TK-NO-'; -- Unless you want this as a parameter

    -- Get the last inserted row in the table
    SET @currentPK = IDENT_CURRENT(‘tablename’);

    -- Extract the sequence number and convert to an int and increment by 1
    SET @sequenceNo = CONVERT(INT, RIGHT(currentPK, 6)) + 1;

    -- Create the new key
    SET @newKey = CONCATE(@prefix, @sequenceNo);
END



See also this article Custom Auto-Generated Sequences with SQL Server[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900