Adding nvarchar values

[RWahlert]

I'm working on a stored procedure that will increment a sored value by 1 when ever a new customer or vendor is created. My customer and vendor numbers are of the format C001234 and V001234 respectively.

I want to increment the stored value after a new customer or vendor is created. In other words I would like C001234 to increment to C001235 after the new customer is saved. I am having problems incrementing the field because it is a string. If I simply write an expression like CustomerID + 1 the value returned is C0012341.

Any ideas how I can do what I want?

Thanks.

Ralph

 

[mrdenny]

Use substring to strip off the first character and incriment the remainint value by one. Add in the leading 0s as needed then add back on the first character.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

And for your update code it's best to make it a single statement so you never have contention problems:

DECLARE @NextID int

UPDATE MyIdTable SET @NextID = CustomerID + 1, CustomerID = CustomerID + 1

-- next value is now in @NextID


[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[RWahlert]

Thanks for the help. I got it all working.

Ralph