# Thread: Converting a formula to SQL help!

1. Registered User
Join Date
May 2010
Posts
1

## Unanswered: Converting a formula to SQL help!

Hello everyone i am new in this forum and i would like your help with a problem that i have been having for some time now: i got a formula in excel that convert ISBN10 to ISBN13, i tried to transit this in SQL but i didnt have much luck only like 10-15% of the ISBN i had is actually correct with my script.

But let me explain to you a bit better how this formula works. As they say an image equals to a thousand words so check out the attachment to see how this formula works. Furthermore, for an original version of the excel you can download from here (unhide the columns between A and F to see full code).

Lastly so i make things a tad easier i will post the code i used in SQL so maybe you can help fine-tune it or tell me whats wrong or even better give me the final code if you like ofc (!)

declare @isbn10 varchar(10),
@isbn13 varchar(13),
@i int,
@chk int

select @isbn10 = '0123456479'

select @isbn13 = '978' + left(@isbn10, 9)

select @i = 1,
@chk = 0

while @i <= 9
begin
select @chk = @chk + (substring(@isbn10, @i, 1) * case when @i % 2 = 1 then 1 else 3 end)
select @i = @i + 1
end

select @chk = 10 - (@chk % 10)

select @isbn13 = @isbn13 + convert(char(1), @chk)

select @isbn13

2. Registered User
Join Date
Jan 2003
Location
British Columbia
Posts
44
The start of the formula in the graphic is Sum(9, 3*7, 8,...). You don't account for this in the SQL procedure. @chk should be initialized to 38 not 0.

The formula multiplies numbers in the odd position by 3. The Case statement causes the even positioned numbers to be multiplied by 3.

3. Registered User
Join Date
Feb 2007
Posts
33
Code:
```declare	@isbn10	varchar(10),
@isbn13	varchar(13),
@i	int,
@chk	int

select	@isbn10	= '0443100489'

select	@isbn13	= '978' + left(@isbn10, 9)

SELECT @chk = (38 + 3*LEFT(@isbn10,1)
+ RIGHT(LEFT(@isbn10,2),1)
+ 3*RIGHT(LEFT(@isbn10,3),1)
+ RIGHT(LEFT(@isbn10,4),1)
+ 3*RIGHT(LEFT(@isbn10,5),1)
+ RIGHT(LEFT(@isbn10,6),1)
+ 3*RIGHT(LEFT(@isbn10,7),1)
+ RIGHT(LEFT(@isbn10,8),1)
+ 3*LEFT(RIGHT(@isbn10,2),1))

select	@chk = 10 - (@chk % 10)

select	@isbn13 = @isbn13 + convert(char(1), @chk)

select	@isbn13```

It might not be as pretty but it appear to work.

4. Registered User
Join Date
May 2010
Posts
1
Hi guys - nice forum you got here!
Last edited by vDim; 05-06-10 at 19:11.

5. Registered User
Join Date
Dec 2011
Posts
1

## MySQL implement with a little correct

use schemaName;
delimiter //
CREATE FUNCTION ISBN10to13(isbn10 VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
# DECLARE isbn10 VARCHAR(10);
DECLARE isbn13 VARCHAR(13);
DECLARE i INT;
DECLARE chk INT;

IF (LENGTH(ISBN10) > 10) THEN
RETURN ISBN10;
ELSE
SET isbn10=SUBSTRING(ISBN10,1,10);
END IF;

# set ISBN10 = '0123456479';
SET isbn13 = CONCAT('978' , LEFT(isbn10, 9));
SET i = 1, chk = 0;

# 9*1+7*3+8*1=38
set chk = (38 + 3*LEFT(isbn10,1)
+ RIGHT(LEFT(isbn10,2),1)
+ 3*RIGHT(LEFT(isbn10,3),1)
+ RIGHT(LEFT(isbn10,4),1)
+ 3*RIGHT(LEFT(isbn10,5),1)
+ RIGHT(LEFT(isbn10,6),1)
+ 3*RIGHT(LEFT(isbn10,7),1)
+ RIGHT(LEFT(isbn10,8),1)
+ 3*LEFT(RIGHT(isbn10,2),1));

SET chk = 10 - (chk % 10);
if (chk<>10) then
SET isbn13 = concat(isbn13 , CONVERT(chk, CHAR(1)));
else
SET isbn13 = concat(isbn13 , '0');
end if;
RETURN isbn13;
END; //
delimiter ;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•