ISBN-10 to ISBN-13

The International Standard Book Number (ISBN) is used as a unique identifier for books. It is not only used to distinguish different titles, but different version of the same book (for more information see the ISBN User's Guide). Starting January 1st, 2007, the number was increased from 10 to 13 digits.

The ISBN-13 can be calculated from the ISBN-10. First, the numbers 978 are put in front of the ISBN-10. The last number of both ISBN-10 & ISBN-13 is a check digit (used to validate the previous numbers) . So the check digit is dropped from the ISBN-10 and a new one is calculated for the ISBN-13.

ISBN-10: 0-060541-71-7
ISBN-13: 978-0-060541-71-2

There are several sites on the web that will do this conversion:

http://www.isbn-international.org/converter/converter.html
http://custompcsoftware.com/isbn13.html

In TIMSS there is one field for ISBN and it will hold 20 characters, so the new size is not an issue. The issue is if you want to maintain both numbers in your database.

I've chosen to just convert all my ISBN number to 13 since we don't deal with them that much and you can find the ISBN-10 based on the ISBN13. If you want to maintain both you can add an extender field, or you could create a view that calls a function that converts one to the other. It's really up to you.

I have created an SQL function to convert 10 to 13 and it's shown below:

CREATE Function dbo.ISBN10TO13(@ISBN10 as varchar(20))
Returns varchar(20) as

Begin
declare @ISBNO varchar(20)
Declare @NewISBN varchar(12)
Declare @ISBN13 varchar(20)
Declare @ISBN13F varchar(20)
Declare @i integer
Declare @n integer
Declare @v integer

--remove any hyphens or spaces
Set @ISBNO=replace(replace(@ISBN10,'-',''),' ','')

If len(@ISBNO)=10 --correct length
Begin
Set @NewISBN = '978' + SubString(@ISBNO, 1, 9)
Set @n = 0
Set @i = 1

While @i<=12 Begin Set @v = SubString(@NewISBN, @i, 1) If @i%2 = 0 Begin Set @n = @n + 3 * @v End Else Begin Set @n = @n + @v End Set @i = @i + 1 End Set @n = @n%10 If @n<>0
Begin
Set @n = 10 - @n
End

Set @ISBN13= @NewISBN+str(@n,1)
-- add hyphens
Set @ISBN13F= substring(@ISBN13,1,3)+'-'+substring(@ISBN13,4,1)+'-' +substring(@ISBN13,5,6)+'-'+substring(@ISBN13,11,2)+'-'+substring(@ISBN13,13,1)
End

Else -- wrong length, return same value
Begin
Set @ISBN13F=@ISBN10
End

Return @ISBN13F
End

Applies to: TIMSS5, TIMSS6, Personify