Reputation: 49
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:
789-0000001
789-0000002
Finally increment should done only 789-0000000
Upvotes: 2
Views: 7784
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
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
Reputation: 755541
The best solution is to use
ID INT IDENTITY(1,1)
column to get SQL Server to handle the automatic increment of your numeric valueSo 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
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