MSSQL: Generate a 8 digit serial/Barcode number from a prefix number

Question

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.

Answer 1

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;