Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

How do I increment a column string value in T-SQL?

Greetings,

     I have a table that has a column called "Control".  This is a nvarchar(25) data type.  I want to write a stored procedure that will copy the latest record into a new record and add a "-xx" value.  For example:

Control: A1234
A call to the SP would yield all of the data from Control 'A1234' and create a new record with 'A1234-1'

A subsequent call would yield 'A1234-2', etc.  
Been struggling with this for awhile and its very urgent.  Thank you.

0
jvalescu
Asked:
jvalescu
  • 10
  • 9
  • 4
2 Solutions
 
BinuthCommented:
try this sample

CREATE PROC spIncrementControl(@nVchControlID NVARCHAR(25))
AS
BEGIN
 
 DECLARE @nVchNewControlID AS NVARCHAR(25)
 
	SELECT 
		@nVchNewControlID = MAX(RIGHT(Control,LEN(Control)-CHARINDEX('-',Control))) + 1 
	FROM TableName
	WHERE CHARINDEX('-',T)  > 0
	AND Control LIKE '%'  +  @nVchControlID + '%'
 
 
 
END

Open in new window

0
 
jvalescuAuthor Commented:
Thank you for the quick response.  I put your suggestions into the code below, but I really need a solution to insert the new record with the new "-xx" suffix.  Thank you!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE SplitCoil
(@CoilNumber varchar(25) )
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
        -- Insert statements for procedure here
    
    DECLARE @nVchNewControlID AS NVARCHAR(25)
 
	SELECT 
		@nVchNewControlID = MAX(RIGHT(@CoilNumber,LEN(@CoilNumber)-CHARINDEX('-',@CoilNumber))) + 1 
	FROM tblCoilLog
	WHERE CHARINDEX('-',CoilNumber)  > 0
	AND @CoilNumber LIKE '%'  +  @nVchNewControlID + '%'
 
 
    
 
END
GO

Open in new window

0
 
BinuthCommented:
try this
CREATE PROCEDURE SplitCoil
(@CoilNumber varchar(25) )
 
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        -- Insert statements for procedure here
    
    DECLARE @intNewControlID AS INT
	DECLARE @NewCoilNumber AS VARCHAR(25)
 
        SELECT 
                @intNewControlID = MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber)))
        FROM tblCoilLog
        WHERE CHARINDEX('-',CoilNumber)  > 0
        AND CoilNumber LIKE '%'  +  @CoilNumber + '%'
 
		SET @NewCoilNumber = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + (ISNULL(@intNewControlID,0) + 1)
    
		-- @NewCoilNumber - will get formated @CoilNumber-xx format
END
GO

Open in new window

0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
jvalescuAuthor Commented:
Thanks again.  I get  the following error when I use the parameter '1234':


Msg 245, Level 16, State 1, Procedure SplitCoil, Line 20
Conversion failed when converting the varchar value '1234-' to data type int.
0
 
BinuthCommented:
Hmm.. sorry missed to cast
change to ..... CAST((ISNULL(@intNewControlID,0) + 1) AS VARCHAR)
CREATE PROCEDURE SplitCoil
(@CoilNumber varchar(25) )
 
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        -- Insert statements for procedure here
    
    DECLARE @intNewControlID AS INT
	DECLARE @NewCoilNumber AS VARCHAR(25)
 
        SELECT 
                @intNewControlID = MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber)))
        FROM tblCoilLog
        WHERE CHARINDEX('-',CoilNumber)  > 0
        AND CoilNumber LIKE '%'  +  @CoilNumber + '%'
 
		SET @NewCoilNumber = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + CAST((ISNULL(@intNewControlID,0) + 1) AS VARCHAR)
    
		-- @NewCoilNumber - will get formated @CoilNumber-xx format
END
GO

Open in new window

0
 
jvalescuAuthor Commented:
Binuth,

   I know this is really close, but I get the same error.  I've included the execute statement that I am testing with and this is the error result:

Msg 245, Level 16, State 1, Procedure SplitCoil, Line 23
Conversion failed when converting the varchar value '1234-' to data type int.

Thank you.

USE [OrderEntry_prodSQL]
GO
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[SplitCoil]
		@CoilNumber = N'1234'
 
SELECT	'Return Value' = @return_value
 
GO

Open in new window

0
 
SharathData EngineerCommented:
create a proc like this and execute.
exec sp_YourProc

create proc sp_YourProc as 
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',Control) = 0 then Control else SUBSTRING(Control,1,CHARINDEX('-',Control)-1) end) from YourTable
select @int = max(convert(int,case when CHARINDEX('-',Control) = 0 then convert(nvarchar(10),0) 
                   else SUBSTRING(Control,CHARINDEX('-',Control)+1,LEN(Control)) end))
  from YourTable where Control like @Control+'%'
select @Control + '-' + convert(nvarchar(10),@int+1)
insert into YourTable (Control) values(@Control + '-' + convert(nvarchar(10),@int+1))

Open in new window

0
 
SharathData EngineerCommented:
check this example
-- Created a temp table to test this
create table TempTable(Control nvarchar(25))
insert into TempTable values('A123'),('A1234'),('A2234-6')
go
-- create the proc
create proc sp_YourProc as 
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',Control) = 0 then Control else SUBSTRING(Control,1,CHARINDEX('-',Control)-1) end) from TempTable
select @int = max(convert(int,case when CHARINDEX('-',Control) = 0 then convert(nvarchar(10),0) 
                   else SUBSTRING(Control,CHARINDEX('-',Control)+1,LEN(Control)) end))
  from TempTable where Control like @Control+'%'
select @Control + '-' + convert(nvarchar(10),@int+1)
insert into TempTable (Control) values(@Control + '-' + convert(nvarchar(10),@int+1))
go
-- execute the proc
exec sp_YourProc -- A2234-7
exec sp_YourProc -- A2234-8
exec sp_YourProc -- A2234-9
exec sp_YourProc -- A2234-10
exec sp_YourProc -- A2234-10
exec sp_YourProc -- A2234-11
go
-- drop the table to test with some other sample data
drop table TempTable
go
 
-- create the table again
create table TempTable(Control nvarchar(25))
insert into TempTable values('A123'),('A1234')
go
-- execute the proc
exec sp_YourProc -- A1234-1
exec sp_YourProc -- A1234-2
exec sp_YourProc -- A1234-3
exec sp_YourProc -- A1234-4
exec sp_YourProc -- A1234-5

Open in new window

0
 
BinuthCommented:
not getting any error here... if you need new coil number as return parameter use OUTPUT variable


excute modified procedure

then try ....

DECLARE @return_value AS VARCHAR(25)
EXEC SplitCoil 'A1234',@return_value OUTPUT
SELECT @return_value



ALTER PROCEDURE SplitCoil(@CoilNumber varchar(25) ,@return_value VARCHAR(25) OUTPUT)
AS
	BEGIN
 
		SET NOCOUNT ON;
 
		DECLARE @NewCoilNumber AS VARCHAR(25)
		DECLARE @intNewControlID AS INT
		SET @intNewControlID = 0
 
		SELECT 
			@intNewControlID = ISNULL(MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber))),0)
		FROM tblCoilLog
		WHERE CHARINDEX('-',CoilNumber)  > 0 AND CoilNumber LIKE '%'  +  @CoilNumber + '%'
 
		SET @return_value = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + CAST((ISNULL(@intNewControlID,0) + 1) AS VARCHAR)
 
	END

Open in new window

0
 
jvalescuAuthor Commented:
Binuth:

    I entered the code you had above, and executed the following:

DECLARE @return_value nvarchar(25)
EXEC SplitCoil 'CN200',@return_value
SELECT @return_value

The resule is 'NULL' everytime even though I know this value is in my table.  I've tried other values as well and they also return null.  Thanks.

USE [OrderEntry_prodSQL]
GO
/****** Object:  StoredProcedure [dbo].[SplitCoil]    Script Date: 04/08/2009 10:35:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SplitCoil](@CoilNumber varchar(25) ,@return_value VARCHAR(25) OUTPUT)
AS
	BEGIN
 
		SET NOCOUNT ON;
 
		DECLARE @NewCoilNumber AS VARCHAR(25)
		DECLARE @intNewControlID AS INT
		SET @intNewControlID = 0
 
		SELECT 
			@intNewControlID = ISNULL(MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber))),0)
		FROM tblCoilLog
		WHERE CHARINDEX('-',CoilNumber)  > 0 AND CoilNumber LIKE '%'  +  @CoilNumber + '%'
 
		SET @return_value = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + CAST((ISNULL(@intNewControlID,0) + 1) AS VARCHAR)
 
	END

Open in new window

0
 
SharathData EngineerCommented:
Did you check my script?
0
 
jvalescuAuthor Commented:
Sharath,

    Yes, I did.  And it worked very close to what I needed.  One problem I found is if I entered a CoilNumber that did not exist, it would still return what I passed in along with a '-1'.  I need it make sure the CoilNumber passed in exists.  Is this possible?
USE [OrderEntry_prodSQL]
GO
/****** Object:  StoredProcedure [dbo].[sp_SplitCoil]    Script Date: 04/08/2009 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as 
--declare @CoilNumber nvarchar(25),
DECLARE @int int
select @CoilNumber = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end) from TempTable
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0) 
                   else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
  from TempTable where CoilNumber like @CoilNumber+'%'
select @CoilNumber + '-' + convert(nvarchar(10),@int+1)
PRINT @CoilNumber + '-' + convert(nvarchar(10),@int+1)
insert into TempTable (CoilNumber) values(@CoilNumber + '-' + convert(nvarchar(10),@int+1))
SET @return_value = @CoilNumber + '-' + convert(nvarchar(10),@int+1)

Open in new window

0
 
SharathData EngineerCommented:
May be my understanding of your requirement is wrong. I am checking for the maximum CoilNumber from your table and started inserting -1, -2 ...rows. Do you want to pass the CoilNumber to the porc and insert -1, -2 ,... for that CoilNumber only?
0
 
jvalescuAuthor Commented:
Binuth,
   
      I must have been making some kind of mistake.  You proc is working pretty  well. I tested it with several examples and they worked fine.  However, I have a coil #, 08102-0966 which produces this result: 08102-0966-967.  Any ideas?
0
 
jvalescuAuthor Commented:
Sharath, you are correct.  Want to pass the coil number in for that coil only.
0
 
SharathData EngineerCommented:
check this
USE [OrderEntry_prodSQL]
GO
/****** Object:  StoredProcedure [dbo].[sp_SplitCoil]    Script Date: 04/08/2009 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as 
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end) 
 from TempTable where CoilNumber = @CoilNumber
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0) 
                   else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
  from TempTable where CoilNumber = @CoilNumber
select @return_value = (@Control + '-' + convert(nvarchar(10),@int+1))
if @return_value is not null insert into TempTable (CoilNumber) values(@return_value)

Open in new window

0
 
jvalescuAuthor Commented:
Sharath,

     Almost there.  The first time I passed 'A123', it returned A123-1.  Thats correct.  The second time I passed 'A123', it returned A123-1.  After several times I checked the database and it created multiple 'A123-1' records, where it should have incremented each.  Thanks for your help.
     
0
 
SharathData EngineerCommented:
try this.
USE [OrderEntry_prodSQL]
GO
/****** Object:  StoredProcedure [dbo].[sp_SplitCoil]    Script Date: 04/08/2009 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as 
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end) 
 from TempTable where CoilNumber = @CoilNumber
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0) 
                   else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
  from TempTable where CoilNumber like @CoilNumber+'%'
select @return_value = (@Control + '-' + convert(nvarchar(10),@int+1))
if @return_value is not null insert into TempTable (CoilNumber) values(@return_value)
 

Open in new window

0
 
jvalescuAuthor Commented:
Sharath,

       Same result as before.
0
 
SharathData EngineerCommented:
No, i tested that and getting incrementing results. let me have a look again.
0
 
SharathData EngineerCommented:
What is your table name?
0
 
SharathData EngineerCommented:
call the proc like this.

DECLARE @return_value nvarchar(25)
EXEC sp_SplitCoil @CoilNumber = 'A123',@return_value = @return_value output
SELECT @return_value
its returning the value incremented by one each time.

alter proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as 
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end) 
 from TempTable where CoilNumber = @CoilNumber
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0) 
                   else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
  from TempTable where CoilNumber like @CoilNumber+'%'
select @return_value = (@Control + '-' + convert(nvarchar(10),@int+1))
if @return_value is not null insert into TempTable (CoilNumber) values(@return_value)

Open in new window

0
 
jvalescuAuthor Commented:
Thank you Sharath and Binuth!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now