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 |
Geen opmerkingen:
Een reactie posten