SQL Increment a char field

Posted on 2012-08-29
Medium Priority
Last Modified: 2012-08-31
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.
Question by:rwheeler23
  • 3
  • 2

Expert Comment

ID: 38348527
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...

Author Comment

ID: 38348638
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.

Author Comment

ID: 38348660
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38350581
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


Author Comment

ID: 38350818
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.
LVL 25

Expert Comment

ID: 38352913
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).

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question