How to Increment The Number with combination of a String .......?

Question

How to Increment The Number with combination of a String .......?

pavanpala.vja

Mr or Mrs. 500

Points: 577
More actions
May 31, 2012 at 2:00 am

#261941

Hi Guys I have a Data like below shown...
GMP000001
GMP000002
.
.
.
GMP001002
For this data i needed to increment like.
Past_value Present value.
GMP000001 GMP000002
GMP000002 GMP000003
.
.
.
GMP001002 GMP001003
Please guys Help to How to reach my above mentioned requirement........?
Pavan Kumar. Pala[font="Verdana"][/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Out of interest, why do you want to increament journal values, typically when a journal is saved it shouldnt be modified (well thats how finance systems I've worked with in the past have worked to comply HMRC)

Any how, this will acheive your required output

declare @tab table (col1 varchar(10))

insert into @tab values ('GMP000001'),('GMP000002'),('GMP000101')

;with cte1 as

(

select col1, LEN(REPLACE(col1, 'GMP','')) length, REPLACE(col1,'GMP','')+1 as newnum from @tab

)

select col1, 'GMP'+REPLICATE('0',length-LEN(newnum))+CONVERT(VARCHAR,newnum) as newcol from cte1

pavanpala.vja Mr or Mrs. 500 Points: 577 More actions · Answer 2

Thanks a lot...anthony.green. i Meet my Requirement.

But Unfortunately i have one more Requirement same like...i need to Insert the Incremented value in other table

Example : GMP000001

For above above value Increment is : GMP000002

so here i need to display only incremented value like 'GMP000002 ' in a column

For that i need a Query Anthony ..

Any way thank u so much for Ur efforts.

Pavan Kumar. Pala[font="Verdana"][/font]

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

pavanpala.vja (5/31/2012)
Thanks a lot...anthony.green. i Meet my Requirement.
But Unfortunately i have one more Requirement same like...i need to Insert the Incremented value in other table
Example : GMP000001
For above above value Increment is : GMP000002
To increment like 'GMP000001 ' to 'GMP000002' value
For that i need a Query Anthony ..
Any way thank u so much for Ur efforts.

use what has been given and modify it and build it into an insert statement

declare @tab table (col1 varchar(10))

insert into @tab values ('GMP000001'),('GMP000002'),('GMP000101')

;with cte1 as

(

select col1, LEN(REPLACE(col1, 'GMP','')) length, REPLACE(col1,'GMP','')+1 as newnum from @tab

)

insert into sometable (somecolumns) select 'GMP'+REPLICATE('0',length-LEN(newnum))+CONVERT(VARCHAR,newnum) as newcol from cte1

pavanpala.vja Mr or Mrs. 500 Points: 577 More actions · Answer 4

Wow.........anthony.green.......Superb.........Its working fine...

Thanks alott...anthony.green

Pavan Kumar. Pala[font="Verdana"][/font]