SQL Increment a char field

Posted on 2012-08-29
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

    Expert Comment

    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

    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

    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
    LVL 25

    Accepted Solution

    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 TABLE DocNumTest
    IF OBJECT_ID('vwDocNumTest') IS NOT NULL
    	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

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now