Author |
Topic |
Vaishu
Posting Yak Master
178 Posts |
Posted - 2007-08-22 : 06:47:18
|
HiI 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" |
|
|
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 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 testinsert test (dummy) values (null)select * from testdrop table test with the help of this function herehttp://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 - 2007-08-22 : 07:33:31
|
HiThanks 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 rowsThanks |
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2007-08-22 : 07:39:56
|
HiI 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
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-22 : 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" |
|
|
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 |
|
|
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" |
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2007-08-22 : 09:02:37
|
Hi everyoneI have two below datacolumns 'code'- varchar 255 (Unique number) data : chr456Umx'Packs'- integer data : 6Is 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 usingCREATE PROCEDURE AMZSelCen@imglink nvarchar(255)ASSelect 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 tablenamewhere ......Thank you very much guys for all your support |
|
|
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] |
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2007-08-22 : 11:06:31
|
HiI 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 IntegerNeed 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
30421 Posts |
Posted - 2007-08-22 : 11:40:37
|
[code]DECLARE @Col1 VARCHAR(10), @Col2 INTSELECT @Col1 = 'AAB56Y', @Col2 = 6SELECT @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" |
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2007-08-22 : 12:34:58
|
Hi PesoI 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 easynessex1 :AAB56Yex2 :MEMBXYUXD2LMex3 :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
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" |
|
|
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 @iselect 'AAB56Y', 6 union allselect 'MEMBXYUXD2LM', 44 union allselect 'CKLFUJBC006-NOBAT', 9select col1, dbo.fnTwitConvert(col1, col2)from @i With the help of this functionCREATE FUNCTION dbo.fnTwitConvert( @Col1 VARCHAR(8000), @Col2 INT)RETURNS CHAR(13)ASBEGIN 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
22864 Posts |
Posted - 2007-08-24 : 05:10:04
|
<<fnTwitConvert>>Good naming convention MadhivananFailing to plan is Planning to fail |
|
|
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 = 5036446100023o 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 sheetPlease 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 3Add 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
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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 usedsee http://www.barcodeisland.com/ean13.phtml KH[spoiler]Time is always against us[/spoiler] |
|
|
Next Page
|