Need to auto increment string in SQL Server 2012

Question

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:

When I update the record 1 it should increment to 789-0000001
When I update the record 2 it should increment to 789-0000002

Finally increment should done only 789-0000000

use combination of auto increment with one extra prefix column value "789" or just put it constant in your code. — Jaydeep Pandya, Nov 28 '15 at 07:54
Why not just have a normal incrementing number and concatenate it to the string 789- when you want to output/display it? — Dijkgraaf, Nov 28 '15 at 07:56
please provide example code since i am beginner @JaydeepPandya — Anbarasan G, Nov 28 '15 at 07:56
Use padding http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 — Dijkgraaf, Nov 28 '15 at 07:59

score 6 · Accepted Answer · answered Nov 28 '15 at 08:23

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.

Slamurai J · Answer 2 · 2021-01-29T14:24:27.123

1

DECLARE @base int = 0

UPDATE TableX

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

FROM
    TableX

edited Jan 29 '21 at 14:24

answered Feb 27 '20 at 16:22

Slamurai J

11
3

2

code only responses are discouraged. Please add some explanation – ihebiheb Feb 27 '20 at 16:36
While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! – Waqar UlHaq Feb 27 '20 at 16:42

score 0 · Answer 3 · edited May 23 '17 at 10:27

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.

score 0 · Answer 4 · edited Nov 28 '15 at 09:04

0

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.

edited Nov 28 '15 at 09:04

Arulkumar

12,966
14
47
68

answered Nov 28 '15 at 08:52

Rohit Padma

603
5
15

1

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Nov 28 '15 at 09:01
@marc_s Thanks :) yeah i started learning how to answer in StackOverflow. – Rohit Padma Jul 03 '17 at 19:33

Need to auto increment string in SQL Server 2012

4 Answers4

Linked