Generating numbers in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Generating numbers in SQL

Author

Topic

sriram_govi
Starting Member

1 Post

Posted - 2004-01-20 : 16:16:48

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.

Any help would be highly appreciated.

thanks,
-sriram

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-20 : 16:25:42

Here ya go:



SET NOCOUNT ON

CREATE TABLE Table1
(
Column1 CHAR(8) NOT NULL
)

INSERT INTO Table1 VALUES('04000001')
INSERT INTO Table1 VALUES('04000002')

INSERT INTO Table1
SELECT SUBSTRING(CONVERT(CHAR(6), GETDATE(), 12), 1, 2) + REPLICATE('0', 6 - LEN(MAX(SUBSTRING(Column1, 3, 6)) + 1)) + CONVERT(VARCHAR(6), MAX(SUBSTRING(Column1, 3, 6)) + 1)
FROM Table1

SELECT Column1
FROM Table1

DROP TABLE Table1

But why not just use an IDENTITY column or a GUID?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources