5

I have a complicated task ahead of me and I really could use some of your brains to help me out.

At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix for an 8 digit barcode, were we currently manually calculate the last 2 digits of the barcode in Excel.

Here are the steps of how we manually calculate the last 2 digits:

Barcode prefix example:

declare @prefix int
set @prefix = 573071

1) Each prefix should be generated 10 times, where the 7th digit auto increments from 0 to 9

5730710
5730711
5730712 
5730713
5730714
5730715
5730716
5730717
5730718
5730719

2) After adding the 7th digit, we use the following formula to calculate the 8th digit for each row:

  • First we start calculating the first row((5730710). Then we begin with calculating the first, third, fifth, seventh digit and multiply by 3 and add them together

    5730710:
    5 x 3     
    3 x 3     
    7 x 3     
    0 x 3 
    
  • Then we take the second, forth, sixth digits and mutiply by 1 and add them together:

    7 x 1
    0 x 1
    1 x 1
    
  • We add both results using SUM (45 + 7 = 53)

  • Then we round up 53 to nearest 10 (upwards only) in this case it has to be 60

    DECLARE @num int = 53;
    SELECT @num + (10 - (@num % 10)); = 60
    
  • After we round up we subtracts the round up value with the original value: (60 - 53 = 7)

....And finally we have generated an 8 digit barcode from a 6 digit prefix: Result: 57307107

So my question is, how do i do this through sql?

Here is how I am calculating the 8th digit:

declare @prefix int
set @prefix = 5730710
Select 
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
) 
+ 
(10 - ((
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)%10))

-
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)

But I'm still missing how to auto generate the 7th digit from 0 to 9 and put it into the above formula, and lastly once the 7th and 8th digits are calculated I need them to be added to the prefix to finally see the final result of 8 digit barcode.

6
DECLARE @prefix INT;
SET @prefix = 573071;
WITH 
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 9)
SELECT num, 
@prefix*100+num*10+9-((@prefix/100000+(@prefix/1000)%10+(@prefix/10)%10+num)*3+((@prefix/10000)%10+(@prefix/100)%10+@prefix%10)-1)%10 barcode
FROM cte1;

fiddle

The option without variable declaration:

WITH 
cte0 AS (SELECT 573071 AS prefix),
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 9)
SELECT num, 
prefix*100+num*10+9-((prefix/100000+(prefix/1000)%10+(prefix/10)%10+num)*3+((prefix/10000)%10+(prefix/100)%10+prefix%10)-1)%10 barcode
FROM cte0, cte1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.