dinsdag 11 januari 2011

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.

Geen opmerkingen: