MySQL Fun: Converting ISBN10 codes to ISBN13

[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 ;