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

• pavanpala.vja

  SSC-Addicted

  Points: 497

  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]

• Ant-Green

  SSC Guru

  Points: 112965

  More actions

  May 31, 2012 at 2:21 am

  #1495043

  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

  SSC-Addicted

  Points: 497

  More actions

  May 31, 2012 at 2:51 am

  #1495057

  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: 112965

  More actions

  May 31, 2012 at 2:54 am

  #1495059

  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

  SSC-Addicted

  Points: 497

  More actions

  May 31, 2012 at 3:24 am

  #1495069

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

  Thanks alott...anthony.green

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