CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3997016
user3997016

Reputation:

How to increase value by 1 in sql server

I have a table in which I have following details:

code
20190904NGH0001
20190904NGH0002
20190904NGH0006
20190904NGH0005

Now I want to get the max data from these data and have to increase value by +1.

So for example if 20190904NGH0006 then in next I need 20190904NGH0007.

So I have written below stored procedure. 

declare @Newcode varchar(100) , @LastCode varchar(MAX)
set @Newcode = 'NGH'
Select @LastCode = MAX(SUBSTRING(code, CHARINDEX(@Newcode, code)+LEN(@Newcode ), LEN(code))) from ticket where codelike '%'+@Newcode+'%'
select @LastCode as newvalue

Form above stored procedure i am getting below output :

newvalue
0006

So to increase I have tried:

Select @LastCode = MAX(SUBSTRING(ticketreference, CHARINDEX(@code, ticketreference)+LEN(@code), LEN(ticketreference)))+1  from ticket where ticketreference like '%'+@code+'%'

But I am getting only 7. I need 0007.

So finally I need to increase +1 in max value.

Example: if 0100 is the max than I need 0101

Upvotes: 0

Views: 1021

Answers (5)

chandrashekhar Dhamal
chandrashekhar Dhamal

Reputation: 1

You Can Try This.Perfect Answer

declare @Newcode varchar(100) ,
@LastCode varchar(MAX)
set @Newcode = 'NGH'
Select @LastCode =max(REPLACE((STR
(SUBSTRING(code,(CHARINDEX(@Newcode, code)+LEN(@Newcode))
         ,LEN(code))+1,
       len(code)-(CHARINDEX(@Newcode, code)+LEN(@Newcode)-1) 
))
,' ',0))
from ticket where  code like '%'+@Newcode+'%'

select @LastCode as newvalue

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

You may try this. You can pass any Alphanumeric string having integer at the last of string. It will take the integer part from the last and increment it by 1 to make same formatted new string.


declare @ss varchar(15) = '20190904NGH0009'
; with cte as(
select Substring(@ss, 1, Len(@ss) - PATINDEX('%[^0-9]%', reverse(@ss))+1) as suf,
Substring(@ss, Len(@ss) - PATINDEX('%[^0-9]%', reverse(@ss))+2, len(@ss)) as pref 
)
select suf + 
REPLICATE('0', len(pref) - len(cast(pref as int)+1)) +     
cast( cast(pref as int)+1 as varchar(10)) 
from cte

--- cast(pref as int)+1 this is portion where we add 1. you may chnge 1 to any number as per your need

Upvotes: 0

Bamidelzz
Bamidelzz

Reputation: 106

Assuming your stored procedure has gotten the last value as 0006, then you can use this to get the next Value:

Declare @LastValue varchar(4), @NewValue varchar(4)
select @LastValue = '0006'
select @NewValue = right ( ('0000' + ltrim(rtrim(str(convert(int,@LastValue)+1))) ),4)
select @NewValue

Hope this helps...

Upvotes: 1

Red Devil
Red Devil

Reputation: 2393

Try this:

select t+cnt as code from (
select substring(code,1,len(code)-1) as t,convert(varchar(50),max(substring(code,len(code),1) + 1)) cnt from YOURTABLE
group by substring(code,1,len(code)-1)
) abc

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try this below option-

DECLARE @S VARCHAR(MAX)  = '20190904NGH0001'

SELECT  
SUBSTRING(@S,1,LEN(@S)-4) + 
REPLICATE(
    '0', 
    4 - LEN(CAST(CAST(RIGHT(@S,4) AS INT) + 1 AS VARCHAR))
) + 
CAST(
    CAST(RIGHT(@S,4) AS INT) + 1 AS varchar
)

Upvotes: 0

Related Questions