generate incrementing numbers

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???

Thanks
LVL 1
nzfireAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT 'SD'+RIGHT('000'+CAST(CAST(RIGHT(urColumn,10) AS BIGINT)+1 AS nVArchar(10)),10)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
nzfireAuthor Commented:
Hi AngelIII

Thats what I have been thinking...

I'll try your code aneeshattingal.
0
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.