generate incrementing numbers

Posted on 2006-05-04
Last Modified: 2008-03-17
Hi All,

How can I generate incrementing number with text in the value, in sql2000. For instance an invoice number, SD0008762871.. the next number I would want would be SD0008762872

Is there an easy way to do it???

Question by:nzfire
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    SELECT 'SD'+RIGHT('000'+CAST(CAST(RIGHT(urColumn,10) AS BIGINT)+1 AS nVArchar(10)),10)
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Here is an example

    declare @tab Table (col varchar(300))
    insert into @tab SELECT 'SD0008762871'
    insert into @tab SELECT 'SD0008762872'
    INSERT INTO @tab SELECT 'SD0008762873'
    SELECT 'SD'+RIGHT('000'+CAST(CAST(RIGHT(MAX(COL),10) AS BIGINT)+1 AS VArchar(10)),10)
    FROM @tab
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    note that you should NOT store this kind of things in a single column, but keep the parts apart, possibly have a computed column for the display value...
    LVL 1

    Author Comment

    Hi AngelIII

    Thats what I have been thinking...

    I'll try your code aneeshattingal.
    LVL 142

    Accepted Solution

    create table invoice
    ( invoice_id int identity ( 209040, 1 )
    , invoice_prefix varchar(10) default 'SD'
    , invoice_prefix + right ( '0000000000' + cast ( invoice_id as varchar(10) ) , 10) as invoide_display
    , <other fields>

    this way, inserting into the table invoice (without specifying invoice_id, invoice_prefix and invoice_display), those 3 values will be filled in automatically.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 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

    8 Experts available now in Live!

    Get 1:1 Help Now