sql server - MS SQL 2005 - Increment a number inside a String data -

in 1 of column value , sequence stored together. example

country= product= shoe item code us000shoe

if same country , same product exists product id us001shoe.

as can see, each time product added country have increment number one. how should number string value , increment it?

why don't have 3 columns

country | an_auto_increment_column | product

(in sql-server auto_increment identity column)

to product code build on fly

select country + right('000' + an_auto_increment_column, 3) + product your_brandnew_productid yourtable

when you'd insert table wouldn't have care incrementing yourself.

Employment