Validate International Securities Identification Number: Difference between revisions

=={{header|Transact-SQL}}==

<lang 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

</lang>

Testing

<lang Transact-SQL>

-- Testing. The following tests all pass.

;WITH ISIN_Tests AS

( SELECT 'US0378331005' AS ISIN, 1 Expected

UNION SELECT 'US0373831005',0

UNION SELECT 'U50378331005',0

UNION SELECT 'US03378331005',0

UNION SELECT 'AU0000XVGZA3',1

UNION SELECT 'AU0000VXGZA3',1

UNION SELECT 'FR0000988040',1

UNION SELECT '0___garbage',0

UNION SELECT '',0

)

SELECT ISIN, Expected, dbo._ISINCheck(ISIN) AS TestResult FROM ISIN_Tests ORDER BY ISIN

</lang>