Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    20

    Unanswered: Auto Number Column

    How do I generate autonumber for a column which will follow a certain format. Say I want it to S001, S002, S003 and etc. so i want the 1st word to be S. And I want this auto number to be pri key as well..

    pls advise.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a computed column which is a concatenation of a CHAR(1) and an IDENTITY column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    20
    thanks a lot..

    i have forgotten all about SQL and that sucks!

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    declare @t table(UserId int, Username varchar(12))
    insert into @t select
    1, 'sdgr' union all select
    2, 'dfgd' union all select
    3, 'kafgdfvi' union all select
    14, 'hrtyy'

    DECLARE @refNo VARCHAR(50),
    @referencenumber VARCHAR(32)

    SELECT @ReferenceNumber = ''

    SELECT @ReferenceNumber = MAX(userid)
    FROM @t

    PRINT @ReferenceNumber

    IF ISNULL(@ReferenceNumber,'') = ''
    SELECT @ReferenceNumber = 'S'+ '001'
    ELSE
    SELECT @ReferenceNumber = 'S'+ RIGHT('00' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 3)

    SELECT @referencenumber

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy +1
    Bklr -1 - you need to wrap all that stuff into transactions and lock the table, making all inserts serial, and you'd need to adapt to perform inserts of any significant size. Fine if you have no option but with SQL Server you do have one.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •