Generating numbers in SQL

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,
-sriramWhat are you doing this for? Uniqueness for records in a table? You can use an Identity Column for that...what is the business problem you are trying to solve?|||thanks for your reply.

My client wants to do it in the way i explained before. Is there a way??

-sriram|||You could do it a few ways, the incrementing number being the point of divergence. To get the current month you simply use the Month() function with GetDate()

ex:
SELECT Month(GetDate())

There are a number of ways to build the second part...

One would be to create an identity column and after the insert read the SCOPE_IDENTITY(), combine it with the Month(GetDate()) to get your number. The problem with this is that it relies on work after the insert which could be done through a stored proc or a trigger.

Another approach would be to select the top value from the column, trimming the month prefix and the creating your value by incrementing that number.

Do you need to stuff with zeros? Does the month need to be 1 or 01; does the second number need to be led by zeros?|||hi,

thanks for replying.

The second number needs to be led with zeros.

thanks,

-sriram|||You can use the SQL-Server Function Replicate() for this:

DECLARE @.MaxLength AS int
SET @.MaxLength = 6
DECLARE @.INT AS int
SET @.INT = 1
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 000001
SET @.INT = 1234
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 001234

|||Here is some code that will help; I tried not to use UDFs because I dont know what version of SQL you are using. Regardless this will show you how to parse the old value out and generate a new one.

-- Assumes that the fixed length is 10

Declare @.OldValuechar(10)
Set@.OldValue= '0100000365'

Declare @.OldNumberint
Set@.OldNumber= Cast(Substring(@.OldValue,3, LEN(@.OldValue)-2) as int)

Declare @.MonthNamevarchar(10)
Set@.MonthName= DateName(M, Cast(Left(@.OldValue,2) + '/01/2000' as datetime))

-- Show the old values
select @.OldValue
select@.OldNumber
select @.MonthName

Declare @.MonthPrefixchar(2)
Set@.MonthPrefix= (Select Case WHEN Month(GetDate()) >= 10 THEN Cast(Month(GetDate()) as char(2)) ELSE '0' + Cast(Month(GetDate()) as char(1)) END)

Declare @.NewNumbervarchar(8)
Set@.NewNumber= Cast(@.OldNumber + 1 as varchar)

Declare @.Prefix varchar(7)
Set@.Prefix= ''

While Len(@.Prefix) < (7 - len(@.NewNumber))
Set @.Prefix = '0' + @.Prefix

Declare @.NewValuechar(10)
Set@.NewValue= @.MonthPrefix + @.Prefix + Cast(@.NewNumber as char)

-- Show the New Value
Select @.NewNumber
Select@.NewValue|||thanks,

i think it makes sense.

it should work..

anyway thanks for your reply.

-sriramsql