Efficient calculation of an ISBN-13 check digit
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
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.