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