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
 General SQL Server Forums
 New to SQL Server Programming
 How to generate random UNIQUE 13 digit number?

Author  Topic 

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-22 : 06:47:18
Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 06:59:27
Use an IDENTITY column. It is not random, but it is unique.
How many unique numbers do you want?

What are you using the barcode for?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 07:22:07
Since last digit is a check digit, you only need 12 characters.
An INT is 10 characters. Add any two ledaing digits, maybe '55' to the IDENTITY number.

use this code
CREATE TABLE	dbo.Test
(
RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
Dummy TINYINT,
EAN13 AS dbo.fnGetLuhn('55' + RIGHT('000000000000' + CONVERT(VARCHAR, RowID), 10))
)


select * from test

insert test (dummy) values (null)

select * from test

drop table test
with the help of this function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76195



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-22 : 07:33:31
Hi
Thanks for the reply. But I don't want to create any table or insert. Is it posible to do in the select statment in sql procedure. I am about to generate this number (13) for 700 rows

Thanks
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-22 : 07:39:56
Hi
I am new. So could please give me some sample code. For example I have column name 'Packs' - int. So am I able to use somthing like

ex. Packs + random number of 13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 07:54:01
With valid check digit?
SELECT	dbo.fnGetLuhn(CONVERT(VARCHAR, CURRENT_TIMESTAMP, 112)
+ RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(36), NEWID()), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), '-', '')
+ REPLACE(CONVERT(VARCHAR, CURRENT_TIMESTAMP, 114), ':', '')
+ CONVERT(VARCHAR, @@PACK_RECEIVED)
+ CONVERT(VARCHAR, @@PACK_SENT), 12))



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 07:58:37
This is smaller
SELECT	RIGHT(CAST(CAST(NEWID() AS VARBINARY(36)) AS BIGINT), 13)
However, this is not guaranteed to be unique.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sbalaji
Starting Member

48 Posts

Posted - 2007-08-22 : 08:35:22
Another way of doing it
select floor(rand()* 10000000000000)
but column u have said cannot be int,it should be of type bigint

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 08:56:42
Will that be unique?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-22 : 09:02:37
Hi everyone

I have two below datacolumns

'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6

Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums


below is the sample procedure I am using


CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)

AS

Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],

--generate 13 digit number

make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'

from tablename
where ......

Thank you very much guys for all your support
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-22 : 10:39:39
Vaishu,

1. What is your actual requirement ?
2. What is the barcode type ?



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-22 : 11:06:31
Hi

I am trying to generate 13 digit code for the product to list on the website.(3400 rows). The below is the two data colums and I am using stored procedure.

ex. Col1's data is 'AAB56Y'- datatype Varchar
Col2's data is '6' - datatype Integer

Need to convert data from both column as 13 digit number as Col3. Col1 is unique in our database so I will get unique 13 digit number(Not RANDOM NUMBER).Please forget Barcode type as I don't need to worry about now.

Very Very sorry about my previous posting asking for random number.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 11:40:37
[code]DECLARE @Col1 VARCHAR(10),
@Col2 INT

SELECT @Col1 = 'AAB56Y',
@Col2 = 6

SELECT @Col1 AS Col1,
@Col2 AS Col2,
@Col1 + REPLICATE('0', 13 - LEN(@Col1) - LEN(@Col2)) + CONVERT(VARCHAR, @Col2) AS Col3[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-22 : 12:34:58
Hi Peso

I got the answer as this AAB56Y0000006 from your solution.

But need All as numbers like 1125625000006 (i.e A = 1, A = 1, B =2, 5, 6, Y = 25, 0,0,0,0,0,6)

Also I have 3400 rows and Col1 has data like below and I have narrawed down to One colum 'Col1' for easyness

ex1 :AAB56Y
ex2 :MEMBXYUXD2LM
ex3 :CKLFUJBC006-NOBAT

So when convert or replace this as a number and If the numbers reaches more than 13 digits, will take first 13 digit as Col3.

I understand this is very hard but posting with little hope.

Many thanks for peso


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 12:44:03
It should not be this hard IF YOU ONLY LEARNED HOW TO SHARE VITAL INFORMATION.





E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 12:54:50
Now we have been forced to guess what you want all day!
I hope you are really satisfied now.
declare @i table (col1 varchar(50), col2 int)

insert @i
select 'AAB56Y', 6 union all
select 'MEMBXYUXD2LM', 44 union all
select 'CKLFUJBC006-NOBAT', 9

select col1,
dbo.fnTwitConvert(col1, col2)
from @i
With the help of this function
CREATE FUNCTION dbo.fnTwitConvert
(
@Col1 VARCHAR(8000),
@Col2 INT
)
RETURNS CHAR(13)
AS
BEGIN
DECLARE @Index SMALLINT,
@Size SMALLINT,
@Result VARCHAR(13),
@Item CHAR(1)

SELECT @Index = 1,
@Size = LEN(@Col1),
@Result = ''

WHILE @Index < 14 AND @Index < @Size
SELECT @Item = SUBSTRING(@Col1, @Index, 1),
@Result = @Result + CASE
WHEN @Item BETWEEN '0' AND '9' THEN @Item
WHEN @Item BETWEEN 'a' AND 'z' THEN CONVERT(VARCHAR, ASCII(UPPER(@Item)) - 64)
ELSE ''
END,
@Index = @Index + 1

IF LEN(@Result) + LEN(@Col2) > 12
SET @Result = @Result + CONVERT(VARCHAR, @Col2)
ELSE
SET @Result = @Result + REPLICATE('0', 13 - LEN(@Result) - LEN(@Col2)) + CONVERT(VARCHAR, @Col2)

RETURN @Result
END



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 05:10:04
<<
fnTwitConvert
>>

Good naming convention

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 07:27:11
Hi peso thank you very much for your effort. We are selling customized products and we need the barcode for the products which will be listed in amazon under new products categoris or lines.I have sorted out with a formula in excelsheet. However the below information will be help ful for the people trying in sql.

EAN -13 - It is barcode format used in europe, (UPC for america). The below link will tell you how to calculate the barcode.

[url]http://www.morovia.com/education/utility/upc-ean.asp[/url]

Number to convert as barcode 503644610002
output will be EAN barcode = 5036446100023

o stands for 'Odd'
e stands for 'Even'

country code| manufactur code| Product code | Checkdigit
5 0 | 3 6 4 4 6 | 1 0 0 0 2 | ?
e o e o e o e o e o e o
A B C

Counrtycode, manufacture code will assigned by EAN organisation. So I need to create 900 barcodes in order to upload to amation.According to abbove link about EAN and my needs I have done the following in Excell sheet

Please visit the hyperlink before you look in to the next line

((11+A+C)*3+(18+B))/10 = Reminder - 10 = Last digit of the EAN is 3
Result should be 503644610002 3

Add everything as string and the last digit will be the 13th digit of barcode from the calculation. So this is excel.

If you want in SQl . You can figure out how to use the 'Autonumber' colum (A B C in my case). So evry time you run the sql procedure you will get the same barcode number (13 digit)for the product because of the 'Autonumber' colum. So the allocated barcode will not be issued for the other Product and will help for inventory and in manyways.







Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 07:31:38
Why don't why READ your answers I give?
I already have given you the fnGetLuhn function which calculates this for you.
It is not that hard to rewrite to your specifications.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 07:45:25
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88376




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-24 : 09:42:16
use 20-29 (In-Store Functions) as country code if you are generating ean13 for internal used
see http://www.barcodeisland.com/ean13.phtml


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
    Next Page

- Advertisement -