jcfiala: (Default)
[personal profile] 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 ;

From:
Anonymous( )Anonymous This account has disabled anonymous posting.
OpenID( )OpenID You can comment on this post while signed in with an account from many other sites, once you have confirmed your email address. Sign in using OpenID.
User
Account name:
Password:
If you don't have an account you can create one now.
Subject:
HTML doesn't work in the subject.

Message:

 
Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.

October 2017

S M T W T F S
1 234567
891011121314
15161718192021
22232425262728
293031    

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Oct. 18th, 2017 06:38 pm
Powered by Dreamwidth Studios