locked
Auto generated no. RRS feed

  • Question


  • Hello everyone,

    Im fairly new to SQL Server but ive got to make a test-engine with a database for a school. There is one thing that i cannot figure out. Is there a way to have a field auto-generate a unique number for each new entry, but the number has to have the prefix "ABC".

    I have got a fair few entrys so already so i would need to input their ID number manually.

    So far i have got ABC0001 - ABC0128, so i would need the numbers to carry on from there. I've tried the IDENTITY function, but it says that this feature cannot be used on any field with a data type (varchar).

    Any help would be great.

    Thank you

    Bourne

    Friday, June 21, 2013 6:54 PM

Answers

  • Please see the eg. bellow

    CREATE TABLE #MyTable
    (
    ID TINYINT NOT NULL IDENTITY (1, 1),
    FirstCol varchar(10) NOT NULL,
    ComputedCol AS ('ABC'+right('000000000000'+cast(id as varchar(7)),7))
    ) ON [PRIMARY]
    
    insert into #MyTable(FirstCol) values('a')
    go 1000
    
    select * from #MyTable
    computed column
    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Friday, June 21, 2013 7:16 PM update
    • Marked as answer by Gru's Minion Friday, June 21, 2013 8:14 PM
    Friday, June 21, 2013 7:15 PM

All replies

  • Please see the eg. bellow

    CREATE TABLE #MyTable
    (
    ID TINYINT NOT NULL IDENTITY (1, 1),
    FirstCol varchar(10) NOT NULL,
    ComputedCol AS ('ABC'+right('000000000000'+cast(id as varchar(7)),7))
    ) ON [PRIMARY]
    
    insert into #MyTable(FirstCol) values('a')
    go 1000
    
    select * from #MyTable
    computed column
    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Friday, June 21, 2013 7:16 PM update
    • Marked as answer by Gru's Minion Friday, June 21, 2013 8:14 PM
    Friday, June 21, 2013 7:15 PM
  • Thank you. But could you explain the code, please? I cannot understand it completely...

    Friday, June 21, 2013 7:21 PM
  • Its just using computed column, please read the link i provided. 

    right('000000000000'+cast(id as varchar(7)),7)

    right is a string manipulation function .  '000000000000'+cast(id as varchar(7)) ;  create a string like 

     0000000000001, 

      0000000000002 etc..

    right('0000000000002',7) returns  0000002 

    vt

     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, June 21, 2013 7:36 PM