Friday, March 23, 2012
Generating Unique Check Digits
I've implemented a small routine that generates "ID" numbers in hexadecimal
format. Does anyone know of any routines to generate a unique check digit
for a hexadecimal number? I've found several ways to do it in Base 10, but
not so much for hex. I really like the UCC mod 10(3) methods, but the check
digits are fairly linear in progression when I try to apply it to hex.
Here's an example of the types of ID's I'll be generating:
B951-D300-1E73-C1xx
Note that the dashes are just to "prettify it", and can be ignored for my
purposes. "xx" represents a placeholder for the 2 hexadecimal check digits.
B951D3001E73C1xx
Thanks. Any help is appreciated.Michael,
Do you need the check digit for more than
catching transcription or typing errors? Is there
a reason "fairly linear" in progression is not acceptable?
Steve Kass
Drew University
Michael C# wrote:
>Hi all,
>I've implemented a small routine that generates "ID" numbers in hexadecimal
>format. Does anyone know of any routines to generate a unique check digit
>for a hexadecimal number? I've found several ways to do it in Base 10, but
>not so much for hex. I really like the UCC mod 10(3) methods, but the chec
k
>digits are fairly linear in progression when I try to apply it to hex.
>Here's an example of the types of ID's I'll be generating:
>B951-D300-1E73-C1xx
>Note that the dashes are just to "prettify it", and can be ignored for my
>purposes. "xx" represents a placeholder for the 2 hexadecimal check digits
.
>B951D3001E73C1xx
>Thanks. Any help is appreciated.
>
>|||Like you mentioned, catching transcription errors is part of it; I also
don't want people to be able to reverse engineer and guess the previous ID
code; for instance, if the code ends with -C1B6, I don't want them to be
able to take a wild guess at -C0B5 being the previous ID.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:OYxYyK7SFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Michael,
> Do you need the check digit for more than
> catching transcription or typing errors? Is there
> a reason "fairly linear" in progression is not acceptable?
> Steve Kass
> Drew University
>
> Michael C# wrote:
>sql
Wednesday, March 21, 2012
Generating numbers in SQL
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:
|||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.
DECLARE @.MaxLength AS int
SET @.MaxLength = 6
DECLARE @.INT AS intSET @.INT = 1
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 000001SET @.INT = 1234
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 001234
-- 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