locked
How to generate alpha numeric serial no in store procedure RRS feed

  • Question

  • User264732274 posted

    generate auto alpha numeric id in sql server like ACX-01, ACX-02, ACX-10, ACX-100, ACX-1000

    suppose first 4 char is fixed and numeric part will be like 01,02.....100,101....1000 upto 1000000

    the serial no will be inserted into db fields. so we have to fetch numeric part first and then increment.

    how to achieve with normal code in store proc and also how to do it with sequence.

    thanks

    Thursday, September 22, 2016 9:40 AM

Answers

  • User264732274 posted

    i got my answer. without any store proc how easily we can do this

     CREATE TABLE tbEmployee
    (
                    EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                    EmpCode AS 'EMP' + RIGHT('0000'+ CONVERT(VARCHAR(5),EmpId),5) PERSISTED,
                    EmployeeName VARCHAR(50),
                    Age        INT,
                    Gender VARCHAR(10)
    )
    --INSERT RECORDS IN TABLE
    INSERT INTO tbEmployee (EmployeeName,Age,Gender)VALUES
    ('Mayank',25,'Male'),
    ('Ankita',24,'Female'),
    ('Anuj',26,'Male'),
    ('Jaswinder',25,'Male'),
    ('Simran',25,'Female') 
    
    --CHECK INSERTED RECORD
    SELECT * FROM tbEmployee

    code taken from http://www.webcodeexpert.com/2015/08/auto-generate-auto-incremented-unique.html

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 11:54 AM

All replies

  • User-595703101 posted

    Hi sudip,

    I used a computed column which adds the fixed string and the identity column of the table as I described at tutorial Custom Sequence String as SQL Identity Column in SQL Server

    The idea is implemented as follows

    CREATE TABLE tbl...
    (
     id int IDENTITY(1,1),
     SerialColumn as dbo.ComputedColFunction('fixedstring', CAST(id as nvarchar(10)),6,'0'),
     ....
    )

    I hope it is useful

    Friday, September 23, 2016 4:59 AM
  • User264732274 posted

    not very clear. need sample code which i can run at my end.

    Friday, September 23, 2016 9:25 AM
  • User264732274 posted

    i got my answer. without any store proc how easily we can do this

     CREATE TABLE tbEmployee
    (
                    EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                    EmpCode AS 'EMP' + RIGHT('0000'+ CONVERT(VARCHAR(5),EmpId),5) PERSISTED,
                    EmployeeName VARCHAR(50),
                    Age        INT,
                    Gender VARCHAR(10)
    )
    --INSERT RECORDS IN TABLE
    INSERT INTO tbEmployee (EmployeeName,Age,Gender)VALUES
    ('Mayank',25,'Male'),
    ('Ankita',24,'Female'),
    ('Anuj',26,'Male'),
    ('Jaswinder',25,'Male'),
    ('Simran',25,'Female') 
    
    --CHECK INSERTED RECORD
    SELECT * FROM tbEmployee

    code taken from http://www.webcodeexpert.com/2015/08/auto-generate-auto-incremented-unique.html

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 11:54 AM