Efficient calculation of an ISBN-13 check digit

Josef RichbergJosef Richberg
1 min read

I thought I might pass along, what I have found to be, the most efficient way to validate the check digit within Azure SQL Server. I was looking to go down the path of a CLR, but it turns out that seems to be frowned upon. The function returns Y/N. For my environment, which is a 24CPU HyperScale, I can process approximately 50,000 isbns/second. My test involves reading ISBNs from a table and outputting the value into a temp table.

CREATE function [dbo].[ValidateISBN]

(@ISBN as bigint)

returns char(1)

as

begin

 declare @is978 tinyint=(978-(@ISBN /10000000000))*-1,

                 @checkdigit tinyint

 set @checkdigit =10-cast((cast((@ISBN %10000000000000/1000000000000) as tinyint)

  + cast((@ISBN %1000000000000/100000000000) as tinyint)*3

  + cast((@ISBN %100000000000/10000000000) as tinyint)

  + cast((@ISBN %10000000000/1000000000) as tinyint)*3

  + cast((@ISBN %1000000000/100000000) as tinyint)

  + cast((@ISBN %100000000/10000000) as tinyint)*3

  + cast((@ISBN %10000000/1000000) as tinyint)

  + cast((@ISBN %1000000/100000) as tinyint)*3

  + cast((@ISBN %100000/10000) as tinyint)

  + cast((@ISBN %10000/1000) as tinyint)*3

  + cast((@ISBN %1000/100) as tinyint)

  + cast((@ISBN %100/10) as tinyint)*3)as tinyint)%10



if ((@checkdigit=10 and @ISBN %10=0) or @checkdigit=@ISBN %10)

        return 'Y'

 return 'N'

end

GO
0
Subscribe to my newsletter

Read articles from Josef Richberg directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Josef Richberg
Josef Richberg

I've been working in and around SQL for over 3 decades. I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone). I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects. Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.