Generate UPC codes with SQL (I just need syntax)

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

SQL Server 2005 Forums

Transact-SQL (2005)

Generate UPC codes with SQL (I just need syntax)

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-09 : 20:14:59

I'm trying to have my SQL database create UPC codes on the fly based on the inputs from my table and. I understand I'll need to create a function that generates the first 11 digits of my UPC code, then another function that will generate the Check Digit for the UPC code. If you're not familiar with how UPC codes work, read on I will show what I know here.

What I have so far is a function that will generate the first 11 numbers I need and a trigger that takes the results of that function and puts it into my table for me.

Function:
CREATE function UPC_Start (@id int)
returns char(11)
as
begin
return '87022' + right('000000' + convert(varchar(11), @id),6)
end

Trigger:
CREATE trigger UPC_Start_Insert on Child_Table
after insert as
update
Child_Table
set
Child_Table.UPC_Start=UPC_Start(Child_Table.ChildID)
from
Child_Table
inner join
inserted on Child_Table.ChildID= inserted.ChildID

This much works perfectly. What I need is the 12th digit called the "Check Digit" of the UPC. Its calculated by taking the digits in the odd numbered locations, adding them up and multiplying them by 3. Then taking the digits in the even numbered locations and adding them to that number. You then take that sum and whatever number you must add to that number to get the next even multiple of 10 is your check digit. For example:

UPC: 870211111303
Odd placed numbers: 8 + 0 + 1 + 1 + 1 + 0 = 11 * 3 = 33
Even placed numbers: 7 + 2 + 1 + 1 + 3 = 14
Sum of Both: 47
Next even multiple of 10 is 50: 50-47 = 3

The next part of the function in SQL I need is something to calculate this Check Digit. I found a post on another forum that gave me this syntax, but I'm new enough to SQL that I can't understand how to adapt it to what I already have.

CREATE PROCEDURE genCheckDigit(@strUPC varchar(11), @checkDigit int output) AS
declare @intTmp1 int
declare @intTmp2 int

set @checkDigit = -1

if isnumeric(@strUPC) = 0
BEGIN
RAISERROR ('UPC is not numeric',16,1)
END

select @strUPC = rtrim(ltrim(@strUPC))
While len(@strUPC) < 11 set @strUPC = '0' + @strUPC

Select @intTmp1 = 0 +
Substring(@strUPC,1,1) +
Substring(@strUPC,3,1) +
Substring(@strUPC,5,1) +
Substring(@strUPC,7,1) +
Substring(@strUPC,9,1) +
Substring(@strUPC,11,1)

select @intTmp1 = @intTmp1 * 3

Select @intTmp2 = 0 +
Substring(@strUPC,2,1) +
Substring(@strUPC,4,1) +
Substring(@strUPC,6,1) +
Substring(@strUPC,8,1) +
Substring(@strUPC,10,1)

select @intTmp1 = @intTmp2 + @intTmp1
select @intTmp1 = (10 - (@intTmp1 % 10)) % 10
select @checkDigit = @intTmp1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-10 : 15:08:06

show us some sample value for which you want to create UPC code...like a row of 10 items. This looks like a fun task

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-10 : 17:50:58

Thanks for the reply. We'll determine if it was fun later! For now, thanks for being willing to help out.

The field for ChildID is the PK of the ChildTable, so the data is sequentially created and inserted on every new item. You can tell that the field for UPC_Start is the 11 digit code that is coming from the function and trigger indicated above. You can see that it essentially adds "87022" to every childID, plus pads the remaining 6 number with a zero in the case that the ChildID is less than 6 digits long. I'd like to create a similar function to generate the UPC_CheckDigit. as stated above, that digit is a mathmatical derivation based on what's included in UPC_Start. The column for UPC then simply becomes a string made up of UPC_Start + UPC_CheckDigit

My first idea was to create these data components separately as I've done here, but then I thought perhaps there was a way to do ALL three steps in one function, then create a trigger to insert the derived value on every new entry.

ChildID| UPC_Start| UPC_CheckDigit| UPC|
37175| 87022037175| Null| Null|
37176| 87022037176| Null| Null|
37177| 87022037177| Null| Null|
37178| 87022037178| Null| Null|
37179| 87022037179| Null| Null|
37180| 87022037180| Null| Null|
37181| 87022037181| Null| Null|
37182| 87022037182| Null| Null|
37183| 87022037183| Null| Null|
37184| 87022037184| Null| Null|
37185| 87022037185| Null| Null|
37186| 87022037186| Null| Null|
37187| 87022037187| Null| Null|

Thanks again for your help. I look forward to your reply.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-10 : 18:19:26

yes you can do it in one step but you first have to massage your existing data. as it stands now, you haev one field for UPC_Start and another one for UPC_CheckDigit. DO you want those fieds merged eventually or you want them to stay as is two seperate fields. Another question for you

if isnumeric(@strUPC) = 0
BEGIN
RAISERROR ('UPC is not numeric',16,1)
END

will whatever you will be converting into UPC always be numeric, alpha or both or it can be any of the above?
And do you want your final UPC to be numeric, alpha, or combination? Can you clarify these please.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-10 : 18:28:52

in one function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[UPC_IT] (@id int)
returns char(50)
as
BEGIN
DECLARE @UpCStart AS CHAR(11)

SET @UpCStart = '87022' + right('000000' + convert(varchar(11), @id),6)

declare @intTmp1 int
declare @intTmp2 int
DECLARE @checkDigit int
set @checkDigit = -1

select @UpCStart = rtrim(ltrim(@UpCStart))

begin
While len(@UpCStart) < 11 set @UpCStart = '0' + @UpCStart
Select @intTmp1 = 0 +
Substring(@UpCStart,1,1) +
Substring(@UpCStart,3,1) +
Substring(@UpCStart,5,1) +
Substring(@UpCStart,7,1) +
Substring(@UpCStart,9,1) +
Substring(@UpCStart,11,1)

select @intTmp1 = @intTmp1 * 3

Select @intTmp2 = 0 +
Substring(@UpCStart,2,1) +
Substring(@UpCStart,4,1) +
Substring(@UpCStart,6,1) +
Substring(@UpCStart,8,1) +
Substring(@UpCStart,10,1)

select @intTmp1 = @intTmp2 + @intTmp1
select @intTmp1 = (10 - (@intTmp1 % 10)) % 10
select @checkDigit = @intTmp1
END
BEGIN
return CAST(@UpCStart AS VARCHAR(20)) + CAST(@checkDigit AS VARCHAR(20))
end
END

CREATE TABLE UPCMango
(
ChildID INT,
UPC_Start CHAR(11),
UPC_CheckDigit INT,
FinalUPC INT
)

INSERT INTO UPCMango (ChildID
)
SELECT '37175'
UNION ALL
SELECT '37176'
UNION ALL
SELECT '37177'
UNION ALL
SELECT '37178'
UNION ALL
SELECT '37179'
UNION ALL
SELECT '37180'
UNION ALL
SELECT '37181'
UNION ALL
SELECT '37182'
UNION ALL
SELECT '37183'
UNION ALL
SELECT '37184'
UNION ALL
SELECT '37185'
UNION ALL
SELECT '37186'
UNION ALL
SELECT '37187'

SELECT dbo.UPC_IT(ChildID) FROM UPCMango

can you confirm the accuracy of the UPC..I am sure there is a better way of doing it than this

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-10 : 20:00:56

It's probably better to have your printing program generate the barcode and check digit. There's a project here that can help:

http://www.codeproject.com/KB/graphics/upc_a_barcode.aspx

If you need to generate the check digit in SQL Server, you can probably modify that code into a CLR stored procedure and register it in your database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-11 : 02:40:39

Why not simply search the SCRIPT library?
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=88378

E 12°55'05.63"
N 56°04'39.26"

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-13 : 15:17:25

Thank you yosiasz!!! I wasn't able to look at this solution until now, but I think you've gotten it figured out.

I checked the CheckDigit and its working properly. See below.

test 37538 870220375383
test1 37539 870220375390
test2 37540 870220375406

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-13 : 17:14:09

I would go with Peso's and other's. why recreate the wheel? The above two have it wrapped neatly

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-13 : 18:35:27

Not quite the best solution for me since I cannot specify the first 5 digits as we did in our code. Our logic is similar and it all works just fine without too much stress on the database. Your solution should work pretty well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources