SQL Server存储过程EAN代码验证

Question

我需要编写一个存储过程或函数来验证在SQL Server中插入EAN13代码。

有人能帮我吗？

Answer 1

已解决，

我创建了一个用于验证的函数：

CREATE FUNCTION[speen]（@ean varchar（max））返回INT AS开始因子INT的声明申报金额INTLen INT声明声明@CC INT声明@CA-INT声明@结果NVARCHAR（最大值）SET@Len=Len（@Ean）SET@Sum=0SET@系数=3如果@Len=14或@Len=13或@Len%12或@Len=8开始当Len>0开始IF@Len 13开始SET@Sum=@Sum+SUBSTRING（@Ean，@Len，1）*@因子SET@因子=4-@因子完长度设置=长度-1完SET@CC=（（1000-@总和）%10）SET@CA=SUBSTRING（@Ean，LEN（@Ean），1）如果@CC=@CA开始SET@Result=0完其他开始SET@Result=1完完其他开始SET@Result=1完RETURN（@Result）完

结果：0至真或1到假

谢谢！

Answer 2

也许你会发现这样的东西更干净。它处理可变长度的输入，还具有可用作检查约束的优点，正如Joe Celko所指出的。

CASE WHEN CAST(SUBSTRING(REVERSE(ean), 1, 1) AS INTEGER) =
        10 - (
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 2, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 3, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 4, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 5, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 6, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 7, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 8, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 9, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 10, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 11, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 12, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 13, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 14, 1)) AS INT)
             ) % 10
THEN 1 ELSE 0 END