MySQL Fun: Converting ISBN10 codes to 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 ;