SQL Increment a char field
Posted on 2012-08-29
I have a field in a table called NDOCNMBR. This field holds the next document number. I need to increment this field everytime a new document is created. This field is defined as char(17) and must remained defined as so. The tricky part is that the users can prefix the numeric part of the field with whatever characters they want to use.
For example: DRC0010001 could be the seed number. So the next document will be DRC0010001 and then DRC0010002 and so on. Now my program is going to be written using VS C#. I am trying to decide do I this conversion in a SQL stored procedure or do I do it in the C# code and then pass it back to SQL. I am going to submit this question to both the C# and SQL groups. Please let me know how this could be coded in either platform and does it really matter which platform actually performs the iteration?
The select statement looks like this: SELECT NDOCNMBR FROM PM40100
There will always only be one record in this table as it is a setup table.
So I need to extract just the numeric portion of this string, increment it and then send it back to the PM40100 so the value will be in place for the next document.