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

Efficient calculation of an ISBN-13 check digit

Comments

More from this blog

Analysis Services models in Azure Fabric -- METADATA$ROW_ID gotcha

Permissions required for user defined table types in Microsoft SQL Server

Azure Data Factory utcNow() nuances

Microsoft SQL Server permission chaining

Using a stored procedure as a source for PowerBI Paginated reports

Command Palette

Comments

More from this blog