Solved

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

Posted on 2009-04-06
23
639 Views
Last Modified: 2012-05-06
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
Comment
Question by:jvalescu
  • 10
  • 9
  • 4
23 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 24084227
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
 

Author Comment

by:jvalescu
ID: 24084288
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
 
LVL 14

Expert Comment

by:Binuth
ID: 24084348
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
 

Author Comment

by:jvalescu
ID: 24085965
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
 
LVL 14

Expert Comment

by:Binuth
ID: 24086563
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
 

Author Comment

by:jvalescu
ID: 24086989
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
 
LVL 40

Expert Comment

by:Sharath
ID: 24090953
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
 
LVL 40

Expert Comment

by:Sharath
ID: 24091021
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
 
LVL 14

Assisted Solution

by:Binuth
Binuth earned 100 total points
ID: 24093715
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
 

Author Comment

by:jvalescu
ID: 24098650
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
 
LVL 40

Expert Comment

by:Sharath
ID: 24098674
Did you check my script?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:jvalescu
ID: 24099593
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
 
LVL 40

Expert Comment

by:Sharath
ID: 24099706
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
 

Author Comment

by:jvalescu
ID: 24099731
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
 

Author Comment

by:jvalescu
ID: 24099742
Sharath, you are correct.  Want to pass the coil number in for that coil only.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24099833
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
 

Author Comment

by:jvalescu
ID: 24099896
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
 
LVL 40

Expert Comment

by:Sharath
ID: 24099950
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
 

Author Comment

by:jvalescu
ID: 24100001
Sharath,

       Same result as before.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24100015
No, i tested that and getting incrementing results. let me have a look again.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24102855
What is your table name?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 400 total points
ID: 24102982
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
 

Author Closing Comment

by:jvalescu
ID: 31567366
Thank you Sharath and Binuth!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now