| Author |
 Topic  |
|
Vaishu
Posting Yak Master
178 Posts |
 Posted - 08/22/2007 : 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
Sweden
30421 Posts |
Posted - 08/22/2007 : 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" |
Edited by - SwePeso on 08/22/2007 07:13:00 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 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 codeCREATE 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 testwith 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" |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 08/22/2007 : 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 |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 08/22/2007 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 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" |
Edited by - SwePeso on 08/22/2007 08:03:09 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 07:58:37
|
This is smallerSELECT 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" |
Edited by - SwePeso on 08/22/2007 08:00:26 |
|
|
|
sbalaji
Starting Member
India
48 Posts |
Posted - 08/22/2007 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 08:56:42
|
Will that be unique?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 08/22/2007 : 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 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 08/22/2007 : 10:39:39
|
Vaishu,
1. What is your actual requirement ?
2. What is the barcode type ?
KH
Time is always against us
|
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 08/22/2007 : 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.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 11:40:37
|
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
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 08/22/2007 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/22/2007 : 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 functionCREATE 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" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 08/24/2007 : 05:10:04
|
<<
fnTwitConvert
>>
Good naming convention 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 08/24/2007 : 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.
http://www.morovia.com/education/utility/upc-ean.asp
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.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/24/2007 : 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" |
Edited by - SwePeso on 08/24/2007 07:33:50 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
|
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
|
|
Topic |
|
How to generate random UNIQUE 13 digit number?