increment string column by 1

[happyIndian100]

I have a Table Product with the following data:

ID productCode Productname
------------------------------
1 sdk/mini/011 AAAA
2 ngs/kitt/2134 BBB
3 smd/ti/022 CCC

now i need to increment all the ProductCodes by one.
the result should be

ID ProductCode ProductName
------------------------------
1 sdk/mini/012 AAAA
2 ngs/kitt/2135 BBB
3 smd/ti/023 CCC

Please help me to write this query..

thx in advance

 

[nigelrivett]

You should hold the product code as separate fields.

update tbl
set productCode = left(productCode,len(productCode)-charindex('/',reverse(productCode))+1) + right(replicate('0',100) + convert(varchar(10),convert(int,right(productCode,charindex('/',reverse(productCode))-1))+1),charindex('/',reverse(productCode))-1)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.