Search

Friday, April 8, 2011

Auto Generate AlphaNumeric ID’s in a SQL Server Table

If you wants to auto generate a Column ID based on an IDENTITY column, then here’s the query.
DECLARE @TT TABLE (CandID as 'S-' + RIGHT('000' + CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT
OUTPUT
SlNo
CardID
CourseID
ReferenceName
1
S-0001
1
Nile
2
S-0002
2
Mani
3
S-0003
3
Aja
4
S-0004
4
Nile
Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.

14 comments:

  1. Thanks for the suggestion.

    Sure, I will change the color scheme.

    ReplyDelete
  2. this code is nice can you able to do like this

    i want to update one column as
    101C_1
    101C_2
    101C_3

    How can i do it?

    ReplyDelete
  3. Use the below query to generate above result:

    DECLARE @TT TABLE (CandID as '101C_' + RIGHT(CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT

    ReplyDelete
  4. i want to set my columns value as
    p101
    p102
    p103

    help please

    ReplyDelete
    Replies
    1. DECLARE @TT TABLE (CandID as 'P' + RIGHT(CONVERT(varchar, CourseID),5),CourseID int IDENTITY(101,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT

      Delete
  5. i want to auto generate 5 letter customer id...where the first letter should be the first letter of the first name of the customer and the other 4 letters should be consisted of the characters,symbols and digits ....how to do it?

    ReplyDelete
    Replies
    1. DECLARE @TT TABLE (CustomerID as SUBSTRING(CumerName, 1, 1) + RIGHT(CONVERT(varchar, CardID),5), CardID int IDENTITY(1001,1),CumerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT

      Delete
  6. i want to auto generate one column is voucher and in this two types generate voucher no
    first type starting number is 1 and second type starting number is 1001 .
    for ex:- the column is voucher and data type is nvarchar(50) and the data is : -
    1
    2
    3
    1001
    1002
    4

    How can i do it?

    ReplyDelete
    Replies
    1. Hi Swati,
      The data should be in proper Order.
      Here I can see that two type of order is running in single column.
      This can not be done.

      Delete
  7. DECLARE @TT TABLE (CandID as 'S-' + RIGHT('000' + CONVERT(varchar, CourseID),5)

    Sir can u please explain me the meaning of this line?

    ReplyDelete
    Replies
    1. CardID columns default value will be
      'S-' plus
      CourseID : four digit numeric number prefixed by zero

      Delete
  8. In this case, when the number reaches 9.. this will happen -:

    S-0008
    S-0009
    S-00010
    S-00011

    But, technically it should be -:

    S-0008
    S-0009
    S-0010
    S-0011

    How to solve this problem?

    ReplyDelete
    Replies
    1. DECLARE @TT TABLE (CandID as 'S-' + RIGHT((CASE WHEN LEN(CONVERT(varchar, CourseID)) = 1 THEN '000' WHEN LEN(CONVERT(varchar, CourseID)) = 2 THEN '00' WHEN LEN(CONVERT(varchar, CourseID)) = 3 THEN '0' ELSE '' END) + CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT
      OUTPUT

      Delete
  9. Nice example.. Seems to help us Newbies..

    However,i have a tricky question that sort of relates to this code..

    I have to generate alphanumeric numbers in a format like this: AC/17/Ass001 or AC/17/Mod001. The 17 indicates the year and the Ass or Mod indicates the course that the person does.. So if its Ass the course will be Assessor etc

    So on an insert of a date field. The trigger or sp must spit out the sequence above..

    How would i achieve this?

    ReplyDelete