CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Anbarasan G
Anbarasan G

Reputation: 49

Need to auto increment string in SQL Server 2012

Consider the table in SQL Server 2012

789-0000000

The above number will be consider as a string in SQL Server 2012, but whenever I update the record I need increment to 1.

For example:

Finally increment should done only 789-0000000

Upvotes: 2

Views: 7784

Answers (4)

Slamurai J
Slamurai J

Reputation: 11

DECLARE @base int = 0

UPDATE TableX

SET 
    TableX.Value = 'Prefix' + RIGHT('0000000' + CAST(@base AS nvarchar),7),
    @base = @base + 1

FROM
    TableX

Upvotes: 1

Rohit Padma
Rohit Padma

Reputation: 603

Declare @str varchar(max) = '789-0000000'

Select 
   SUBSTRING ( @str ,0 ,CHARINDEX ( '-' ,@str  ))
   +'-'
   +
   (SUBSTRING ( @str ,(CHARINDEX ( '-' ,@str)+1) ,(7-LEN(CAST(SUBSTRING ( @str ,CHARINDEX ( '-' ,@str)+1,LEN(@str)) as int))
              )
    )+
   CAST(CAST(SUBSTRING ( @str ,CHARINDEX ( '-' ,@str)+1,LEN(@str)) as int)+1 as varchar))

When @str='789-0001947'

Output @str= 789-0001948

You can write a update trigger on the table with above logic.

Upvotes: 0

marc_s
marc_s

Reputation: 755541

The best solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.YourTable
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CompanyID AS '789-' + RIGHT('000000' + CAST(ID AS VARCHAR(7)), 7) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into dbo.YourTable without specifying values for ID or CompanyID:

INSERT INTO dbo.YourTable(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and CompanyID will contain values like 789-0000001, 789-0000002,...... and so on - automatically, safely, reliably, no duplicates.

Upvotes: 6

aurelius
aurelius

Reputation: 4076

you can split the string e.g.:

SELECT Item
FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')

then cast it e.g.:

SELECT CAST(YourVarcharCol AS INT) FROM Table

then manually increment it

e.g.:

DECLARE max_id INT
SET @max_id = (SELECT MAX(id) FROM source_table)

DECLARE cursor_name CURSOR FOR
SELECT columns, to, copy
FROM source_table

OPEN cursor_name

FETCH NEXT FROM cursor_name
INTO @columns, @to, @cop

at update e.g.:

declare @i int  = SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices

update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

you can understand how complicate this is and why the solution using a constant to store that prefix is right one.

Upvotes: 0

Related Questions