jcfiala

This is a blog post copied from John's Website - please feel free to join him there and post comments. He has set up openid, so you can post there with your livejournal account using your openid, which is the same as your journal url minus the http://. You can find this entry at http://www.jcfiala.net/blog/2013/03/13/mysql-fun-converting-isbn10-codes-isbn13.

So, I'm currently working on a book-related project where I'm processing a bunch of book data, and for some reason the isbn13 values are missing, where the isbn10 values are still there.

So, I went out and found this useful mysql function for converting the values:

delimiter //
CREATE FUNCTION ISBN10to13(isbn10 VARCHAR(50)) 
RETURNS VARCHAR(50) 
BEGIN
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 ;