SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to generate random UNIQUE 13 digit number?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Vaishu
Posting Yak Master

178 Posts

Posted - 08/22/2007 :  06:47:18  Show Profile  Reply with Quote
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
30277 Posts

Posted - 08/22/2007 :  06:59:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/22/2007 :  07:22:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/22/2007 :  07:33:31  Show Profile  Reply with Quote
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 - 08/22/2007 :  07:39:56  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 08/22/2007 :  07:54:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/22/2007 :  07:58:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/22/2007 08:00:26
Go to Top of Page

sbalaji
Starting Member

India
48 Posts

Posted - 08/22/2007 :  08:35:22  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 08/22/2007 :  08:56:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/22/2007 :  09:02:37  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 08/22/2007 :  10:39:39  Show Profile  Reply with Quote
Vaishu,

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



KH
Time is always against us

Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 08/22/2007 :  11:06:31  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 08/22/2007 :  11:40:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts

Posted - 08/22/2007 :  12:34:58  Show Profile  Reply with Quote
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

Sweden
30277 Posts

Posted - 08/22/2007 :  12:44:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30277 Posts

Posted - 08/22/2007 :  12:54:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22765 Posts

Posted - 08/24/2007 :  05:10:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
fnTwitConvert
>>

Good naming convention

Madhivanan

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

Vaishu
Posting Yak Master

178 Posts

Posted - 08/24/2007 :  07:27:11  Show Profile  Reply with Quote
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.







Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/24/2007 :  07:31:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/24/2007 :  07:45:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17658 Posts

Posted - 08/24/2007 :  09:42:16  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000