验证ISIN， SEDOLE和CUSIP

ISIN, SEDOLE和CUSIP都是用于标识金融中的有价证券。说的简单点，他们就是一串字符和数字组成字符串，用来定义像股票，基金等等。他们的区别在于应用的国家和字符本身定义的不同。

 

ISIN的定义可见wikipedia,用于全球唯一标志有价证券。它是一个12位的由字母和数字组成字符串，应用于交易和结算。它一共由三部分组成，两位的国家标识，九位字母数字组成

的标志和一位验证码。最后的一位验证码是通过前面的11位计算得到的。那么验证ISIN就是验证最后一位是否和通过规定确定的算法计算的结果一致。下面描述这种验证的算法：

首先对于字符到数值之间的转换，有 A = 10, B = 11, C = 12……X = 33, Y = 34, Z = 35,在sql中，可以这样得到对应数值：

?

1	SELECT ASCII('A') – 55

?

1	比如对于ISIN：<strong>US0378331005</strong> 最后的一位5是如下计算得到的:

第一步：把字符转换成数字：U->30, S->28

US0378331005 ->3028037833100

第二步：把得到的数字字符串分成两组，奇数位组和偶数位组

3028037833100 = (3, 2, 0, 7, 3, 1, 0), (0, 8, 3, 8, 3, 0)

第三步：把最后一位所在的组每个数字字符乘2(以上最后一位为0，在奇数位组)

(6, 4, 0, 14, 6, 2, 0)

第四步：把所有的数字加起来，对于第三步计算得到的值如果大于10，用个位和十位的值相加。

(6 + 4 + 0 + (1 + 4) + 6 + 2 + 0) + (0 + 8 + 3 + 8 + 3 + 0) = 45

第五步：对10求余

45 mod 10 = 5

第六步：从十相减

10 - 5 = 5

第七步：在对10求余

5 mod 10 = 5

所以最后一位验证码是5，US0378331005 是一个能通过验证的ISIN。

在以上的计算当中，对于单一的数字进行乘2的操作，如果值大于10 用个位去加十位。那么对0，1，2，3，4来说，只要它们乘2就 可以了，但是对5, 6, 7, 8, 9来说，

5->5*2=10->1+0=1

6->6*2=12->1+2=3

7->7*2=14->1+4=5

8->8*2=16->1+6=7

9->9*2=18->1+8=9

它们之间有n->2*n-9的对应关系。首先建立如下函数来做这种简单的计算：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE FUNCTION [dbo].[getSum] (     @inputValue INT ) RETURNS INT AS BEGIN     DECLARE @retV AS INT     SET @retV = @inputValue * 2       IF @inputValue > 4         SET @retV = @retV - 9               RETURN @retV END

?

1	接下来就是写函数来验证ISIN了，从上面的计算过程中，首先要得到由数字的字符串，然后在对这个数字字符串进行奇数位，偶数位的计算。代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

CREATE FUNCTION [dbo].[Is_IsinValid] (     @isin VARCHAR(MAX) ) RETURNS BIT AS BEGIN     DECLARE @retValue AS BIT     SET @retValue = 0           SET @isin =LTRIM(RTRIM(@isin))     DECLARE @numS AS VARCHAR(MAX)       SELECT @numS = COALESCE(@numS, '') + c     FROM (         SELECT  CASE             WHEN ISNUMERIC(SUBSTRING(@isin, N, 1)) = 1 THEN SUBSTRING(@isin, N, 1)             ELSE CAST(ASCII(SUBSTRING(@isin, N ,1))  - 55 AS VARCHAR(MAX)) END AS c         FROM dbo.Number         WHERE N < LEN(@isin)     ) AS dr1       DECLARE @vv AS INT     SET @vv = 0       SELECT @vv = @vv + CASE         WHEN N %2 = LEN(@numS) % 2 THEN dbo.getSum(CAST(SUBSTRING(@numS, N, 1) AS INT))         ELSE CAST(SUBSTRING(@numS, N, 1) AS INT) END     FROM dbo.Number     WHERE N <= LEN(@numS)       IF (10 - @vv % 10) % 10 = SUBSTRING(@isin, LEN(@isin), 1)         SET @retValue = 1           RETURN @retValue END

?

1

?

1	CUSIP的定义见<a href="http://en.wikipedia.org/wiki/Cusip" rel="noopener nofollow">wiki</a>。它是9位有字母和数字组成的字符串。主要在北美使用。具体的计算方式不再描述，wiki中有很详尽的描述，此处只贴上sql代码。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

CREATE FUNCTION [dbo].[Is_CusipValid] (     -- Add the parameters for the function here     @cusip VARCHAR(MAX) ) RETURNS BIT AS BEGIN     SET @cusip = LTRIM(RTRIM(@cusip))           DECLARE @retBit AS BIT     SET @retBit = 0       DECLARE @sumValue AS INT     SET @sumValue = 0       SELECT @sumValue = @sumValue +         CASE WHEN N % 2 = 0 THEN             CASE WHEN ISNUMERIC(SUBSTRING(@cusip, N, 1)) = 1 THEN dbo.getSum(CAST(SUBSTRING(@cusip, N, 1) AS INT))             ELSE dbo.getSum((ASCII(SUBSTRING(@cusip, N, 1)) - 55) / 10)                 + dbo.getSum((ASCII(SUBSTRING(@cusip, N, 1)) - 55 ) % 10)             END         ELSE             CASE WHEN ISNUMERIC(SUBSTRING(@cusip, N, 1)) = 1 THEN CAST(SUBSTRING(@cusip, N, 1) AS INT)             ELSE (ASCII(SUBSTRING(@cusip, N, 1)) - 55) / 10 + (ASCII(SUBSTRING(@cusip, N, 1)) - 55 ) % 10  END         END     FROM dbo.Number     WHERE N < LEN(@cusip)       IF (10 - @sumValue % 10) % 10 = SUBSTRING(@cusip, LEN(@cusip), 1)         SET @retBit = 1               RETURN @retBit END

?

1

?

1	SEDOL是7位字母数字组成的标识，主要应用于英国和爱尔兰。定义见<a href="http://en.wikipedia.org/wiki/SEDOL" rel="noopener nofollow">wiki</a>。计算过程也不再重复，只共享代码。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

CREATE FUNCTION [dbo].[Is_SedolValid] (     -- Add the parameters for the function here     @sedolChars AS CHAR(20) ) RETURNS BIT AS BEGIN       DECLARE @retValue AS BIT     DECLARE @lastItem AS ChAR     SET @retValue = 0           IF LEN(@sedolChars) = 7     BEGIN         SELECT @lastItem = (10 - SUM(ItemValue) % 10) % 10         FROM(             SELECT Item, Weight, ItemValue = (                 CASE ISNUMERIC(Item)                     WHEN 1 THEN CAST(Item AS INT)                     ELSE ASCII(Item) - 55                 END) * Weight             FROM (                 SELECT SUBSTRING(@sedolChars, N, 1) Item, Weight=(                     CASE N                         WHEN 1 THEN 1                         WHEN 2 THEN 3                         WHEN 3 THEN 1                         WHEN 4 THEN 7                         WHEN 5 THEN 3                         WHEN 6 THEN 9                         WHEN 7 THEN 1                         ELSE 0                     END)                 FROM dbo.Number                 WHERE N< LEN(@sedolChars)             ) AS dr1         ) AS dr2                      IF CAST(SUBSTRING(@sedolChars, 7, 1) AS INT) = @lastItem         SET @retValue =1     END           RETURN @retValue END