41 lines
1.5 KiB
Transact-SQL
41 lines
1.5 KiB
Transact-SQL
CREATE FUNCTION dbo._ISINCheck( @strISIN VarChar(40) )
|
|
RETURNS bit
|
|
AS
|
|
BEGIN
|
|
--*** Test an ISIN code and return 1 if it is valid, 0 if invalid.
|
|
DECLARE @bValid bit;
|
|
|
|
SET @bValid = CASE WHEN @strISIN LIKE '[A-Z][A-Z][A-Z,0-9][A-Z,0-9][A-Z,0-9][A-Z,0-9][A-Z,0-9][A-Z,0-9][A-Z,0-9][A-Z,0-9][A-Z,0-9][0-9]' THEN 1 ELSE 0 END
|
|
IF @bValid = 1
|
|
BEGIN
|
|
DECLARE @strTest VarChar(40) = '';
|
|
DECLARE @strAdd VarChar(2);
|
|
DECLARE @p INT = 0;
|
|
WHILE @p < LEN(@strISIN)
|
|
BEGIN
|
|
SET @p = @p+1;
|
|
SET @strAdd = SUBSTRING(@strISIN,@p,1);
|
|
IF @strAdd LIKE '[A-Z]' SET @strAdd = CONVERT(VarChar(2),ASCII(UPPER(@strAdd))-55);
|
|
SET @strTest = @strTest + @strAdd;
|
|
END;
|
|
|
|
-- Proceed with Luhn test
|
|
DECLARE @strLuhn VarChar(40) = REVERSE(@strTest); -- usage: set once, never changed
|
|
DECLARE @strS2Values VarChar(10) = '0246813579'; -- constant: maps digits to their S2 summed values
|
|
SET @p = 0; -- reset loop counter
|
|
DECLARE @intValue INT;
|
|
DECLARE @intSum INT = 0;
|
|
-- loop through the reversed string, get the value (even-positioned digits are mapped) and add it to @intSum
|
|
WHILE @p < LEN(@strLuhn)
|
|
BEGIN
|
|
SET @p = @p+1;
|
|
SET @intValue = CONVERT(INT, SUBSTRING(@strLuhn,@p,1) ) -- value of the digit at position @p in the string
|
|
IF @p % 2 = 0 SET @intValue = CONVERT(INT,SUBSTRING(@strS2Values,@intValue+1,1))
|
|
SET @intSum = @intSum + @intValue
|
|
END
|
|
-- If the of the digits' mapped values ends in 0 (modulo 10 = 0) then the Luhn test succeeds
|
|
SET @bValid = CASE WHEN @intSum % 10 = 0 THEN 1 ELSE 0 END
|
|
END;
|
|
|
|
RETURN @bValid
|
|
END
|