SQL Increment a char field

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.
Who is Participating?
lwadwellConnect With a Mentor Commented:
This might not be what you are looking for ... but an alternative is to use a view in you program that splits the field into 2 ... the prefix and the sequence number.  Then manipulate the view using an INSTEAD OF TRIGGER.
This is a rough example of what I am suggesting:
	DROP VIEW vwDocNumTest

create table DocNumTest (
    DocNumber   varchar(17),
    TestCase    varchar(100)
insert into DocNumTest values
('DRC0010001','Test 1'),
('XXXYDRC0010001','Test 2');

select *
  from DocNumTest;

create view vwDocNumTest as
SELECT LEFT(DocNumber,LEN(DocNumber)-5) as DocNumberPrefix
     , CAST(RIGHT(DocNumber,5) as INT) as DocNumberSeq
     , TestCase
  FROM DocNumTest;

select *
  from vwDocNumTest;

CREATE TRIGGER vwDocNumTestTrig ON vwDocNumTest
    INSERT INTO DocNumTest (DocNumber, TestCase)
    SELECT DocNumberPrefix + RIGHT('00000'+CAST(DocNumberSeq as VARCHAR),5)
         , TestCase
      FROM inserted

insert into vwDocNumTest (DocNumberPrefix, DocNumberSeq, TestCase) values ('zzzz',1,'Test 3');
insert into vwDocNumTest (DocNumberPrefix, DocNumberSeq, TestCase) 
SELECT DocNumberPrefix, DocNumberSeq + 1, 'Test 4'
FROM vwDocNumTest
WHERE DocNumberPrefix = 'DRC00' AND DocNumberSeq = 10001;

select *
  from vwDocNumTest;
select *
  from DocNumTest;

Open in new window

Maybe you can make a new table to counter the last increment for every sequence numbering code...

then read the last count for every time create a new record in your module, + 1, and use the fix format before a new increment result.. after that you need to update the last increment into the counter table if the process is success..

I hope it can solving the problem...
rwheeler23Author Commented:
There is an external application to which this setup table belongs so this table can be updated by two different programs so that idea will not work. As I think about this I am going to try to find the first digit in the string and then start there and go to the right. That will give me the number plus whatever the prefix characters are. Once I increment the number I can then bring the two back together again. I also think it will be best to do this all down in the stored procedure. Now I just have to find the time to code this and make it so.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

rwheeler23Author Commented:
I think something like this is a good starting point. This will find the first digit and then I can cut up the string from there.

DECLARE @intAlpha int
DECLARE @strAlphaNumeric char(17)

SET @strAlphaNumeric = 'RDC001001'

SET @intAlpha = PATINDEX('%[0-9]%', @strAlphaNumeric )

PRINT @intAlpha
rwheeler23Author Commented:
I will give this a shot. One issue is that I need this routine to be universal. So one client may have DRC0010001 and then the next client just has 0010001 and another could have D0010001. So the number of characters is variable.
In my example I fixed the size of the number to 5 ... the prefix was what ever was left over ... so it could be any length from 0 to 12 (with a 17 max).  As you need to allow enough trailing digits to grow to a maximum version ... this should be an acceptable limitation (perhaps 5 is too high ... you are going to be the best judge on that).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.