Convert ISBN10 to ISBN13

Today I did get a request to make it possible to convert ISBN10 numbers to ISBN13 numbers. Based on the C# function of http://www.codeproject.com/Tips/75999/Convert-ISBN10-To-ISBN-13.aspx I created the following T-SQL Function.

(download T-SQL Code)

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* =============================================================== Version Information Author:        Arjan Fraaij Create date: 10-01-2010 Description: Converts ISBN-10 to ISBN-13 Execute as SELECT dbo.udf_ISBN10toISBN13(<ISBN10ColmnName>,<ISBN13ReturnType>) FROM <TableName> <ISBN13ReturnType> when 0 ISBN13 short is returned 9780860014096 <ISBN13ReturnType> when 1 ISBN13 long is returned 978-0-86001-409-6 Example: SELECT dbo.udf_ISBN10toISBN13(ISBN10,0) FROM tblISBNNumbers Version        Date        Auteur                Description 0.1            12-1-2011    Arjan Fraaij        Initial version no error handling Todo - Adding Error handling - Adding check for existing ISBN number =============================================================== */ CREATE FUNCTION udf_ISBN10toISBN13 (     -- Add the parameters for the function here     @ISBN VARCHAR(10),     @ISBNTYPE BIT -- 0 ISBN13_SHORT / 1 ISBN13_LONG ) RETURNS VARCHAR(20) AS BEGIN --- Declare needed variables    DECLARE @Result VARCHAR(18)     DECLARE @ISBN13_LONG VARCHAR(18)     DECLARE @CHECKDIGIT INT     DECLARE @ISBN13_SHORT VARCHAR(13)         DECLARE    @ISBN10_1 VARCHAR(2),@ISBN10_2 VARCHAR(2),@ISBN10_3 VARCHAR(2)     ,@ISBN10_4 VARCHAR(2),@ISBN10_5 VARCHAR(2),@ISBN10_6 VARCHAR(2)     ,@ISBN10_7 VARCHAR(2),@ISBN10_8 VARCHAR(2),@ISBN10_9 VARCHAR(2)     ,@ISBN10_10 VARCHAR(2),@ISBN10_11 VARCHAR(2),@ISBN10_12 VARCHAR(2)     SELECT @ISBN13_SHORT = '978' + SUBSTRING(@ISBN,1,9) ---         ---- Caculate check digit     SELECT @ISBN10_1 = SUBSTRING(@ISBN13_SHORT,1,1)     SELECT @ISBN10_2 = CAST(SUBSTRING(@ISBN13_SHORT,2,1) AS INT) * 3     SELECT @ISBN10_3 = SUBSTRING(@ISBN13_SHORT,3,1)     SELECT @ISBN10_4 = (CAST(SUBSTRING(@ISBN13_SHORT,4,1) AS INT) * 3)     SELECT @ISBN10_5 = SUBSTRING(@ISBN13_SHORT,5,1)     SELECT @ISBN10_6 = (CAST(SUBSTRING(@ISBN13_SHORT,6,1) AS INT) * 3)     SELECT @ISBN10_7 = SUBSTRING(@ISBN13_SHORT,7,1)     SELECT @ISBN10_8 = (CAST(SUBSTRING(@ISBN13_SHORT,8,1) AS INT) * 3)     SELECT @ISBN10_9 = SUBSTRING(@ISBN13_SHORT,9,1)     SELECT @ISBN10_10 = (CAST(SUBSTRING(@ISBN13_SHORT,10,1) AS INT) * 3)     SELECT @ISBN10_11 = SUBSTRING(@ISBN13_SHORT,11,1)     SELECT @ISBN10_12 = (CAST(SUBSTRING(@ISBN13_SHORT,12,1) AS INT) * 3)         SELECT @CHECKDIGIT = 10 - (                                 (                                     CAST(@ISBN10_1 AS INT) +                                     CAST(@ISBN10_2 AS INT) +                                     CAST(@ISBN10_3 AS INT) +                                     CAST(@ISBN10_4 AS INT) +                                     CAST(@ISBN10_5 AS INT) +                                     CAST(@ISBN10_6 AS INT) +                                     CAST(@ISBN10_7 AS INT) +                                     CAST(@ISBN10_8 AS INT) +                                     CAST(@ISBN10_9 AS INT) +                                     CAST(@ISBN10_10 AS INT) +                                     CAST(@ISBN10_11 AS INT) +                                     CAST(@ISBN10_12 AS INT)                                 ) % 10 -- Calculate Modulo                             )     IF (@CHECKDIGIT = 10) SET @CHECKDIGIT = 0     -- Create return values     IF (@ISBNTYPE = 0)         BEGIN             SELECT @Result = @ISBN13_SHORT + CAST(@CHECKDIGIT AS VARCHAR(1))         END     ELSE         BEGIN             SELECT @Result =    SUBSTRING(@ISBN13_SHORT,1,3) + '-' +                                 SUBSTRING(@ISBN13_SHORT,4,1) + '-' +                                 SUBSTRING(@ISBN13_SHORT,5,5) + '-' +                                 SUBSTRING(@ISBN13_SHORT,10,3) + '-' +                                 CAST(@CHECKDIGIT AS VARCHAR(1))         END     -- Return the result of the function     RETURN @Result END GO

You need to create the function in the database that contains the ISBN number or called it with the database prefix from the database where you did create it.

SELECT dbo.udf_ISBN10toISBN13('0860014096',0) will return 9780860014096

SELECT dbo.udf_ISBN10toISBN13('0860014096',1) will return 978-0-86001-409-6

If you have any comment or improvement please let me now.