add string(static) + integer(auto-increment) to a field

• bladerunner148

  SSCommitted

  Points: 1824

  More actions

  September 8, 2009 at 4:26 pm

  #141501

  I need to assign a seed number to a field which is something like this format: ABCD1234. Here, ABCD is going to be static 1 will be added to 1234 each time there is an entry in the database. In order to set a field as auto increment by 1, it has to be an integer datatype right? I want to store this data (ABCD1234) into one field so this can be used in various reports as if needed. What would be the best way to do this?

  I will be getting a list of data that will have these seed numbers. In order my app to start creating new seed number, I would have to get the latest one (SELECT MAX(FieldName)) and then add 1 so I can keep incrementing this seed number by 1. The problem is the existing data will already have ABCD attached to the number...could use a substring function to get the digits after ABCD and then add 1 to it and store the new seed number. I don't know if there is a nice and easy way to do this. Any ideas would be appreciated.

  Thanks!

• Paul White

  SSC Guru

  Points: 150457

  More actions

  September 8, 2009 at 4:48 pm

  #1050840

  Taking you literally:

  SET NOCOUNT ON;

  GO

  CREATE TABLE dbo.A (id INT IDENTITY PRIMARY KEY, silly_key AS 'ABCD' + CONVERT(VARCHAR(15), id))

  GO

  INSERT dbo.A DEFAULT VALUES;

  GO 100

  SELECT *

  FROM dbo.A;

  GO

  DROP TABLE dbo.A

  But...why? Why would you do this?

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi