拆分数字并将其添加到指定权重的逻辑n SQL

Question

我有以下要求。下面是程序。我没有得到预期的输出。请纠正我哪里错了。

0   0   0   0   0   0   1   1   1   5   0   8   0   0   2   0   0   4   0   0   4   0   0   1   0   0   0   0   4   0   0   0   0   0   0   0   0   0   4   3   1   2   4   0   1   1   0   1   1   0   0   0   0   0   0   1   1   1   0                                                                               

从左到右指定权重7、5、3、2：

7   5   3   2   7   5   3   2   7   5   3   2   7   5   3   2   7   5   3   2   6   5   3   2   7   5   3   2   5   5   3   2   7   5   3   2   7   5   3   2   7   5   3   2   7   5   3   2   7   5   3   2   7   5   3   2   7   5   3                                                                               

将每个数字乘以其指定的权重：

0   0   0   0   0   0   3   3   7   25  0   16  0   0   6   0   0   20  0   0   24  0   0   2   0   0   0   0   20  0   0   0   0   0   0   0   0   0   12  6   7   10  12  0   7   5   0   2   7   0   0   0   0   0   0   2   7   5   0                                                                               

拆分和添加数字：

0   0   0   0   0   0   3   3   7   7   0   7   0   0   6   0   0   2   0   0   6   0   0   2   0   0   0   0   2   0   0   0   0   0   0   0   0   0   3   6   7   1   3   0   7   7   7   2   7   0   0   0   0   0   0   2   2   5   0   

总数是104

将总和除以模数10：

104/10 = 10.4

从模10中减去余数：

10-4 =6

校验位：

6

Answer 1

我没有检查你的查询，因为它可以通过基于集合的解决方案来完成。不需要光标。只需使用递归生成一个数表即可。如果你有一个数字表格，使用它

declare @str varchar(100) = '00000011150800200400400100004000000000431240110110000001110'

; with 
number as   -- replace with a number or tally table if you have one
(
    select  n = 1
    union all
    select  n = n + 1
    from    number
    where   n   < len(@str)
),
weights as
(
    select  n = 1, weights = 7  union all
    select  n = 2, weights = 5  union all
    select  n = 3, weights = 3  union all
    select  n = 0, weights = 2
)
select  check_digit = 10 
                    - ( sum(s.d1 + s.d2) % 10 )
from    number n
        inner join weights w    on  n.n % 4 = w.n
        cross apply
        (
            select  d1 = convert(int, substring(@str, n.n, 1)) * w.weights / 10,
                    d2 = convert(int, substring(@str, n.n, 1)) * w.weights % 10
        ) s

您可以使用以下select子句验证间歇性结果

select  n.n, 
        digit = convert(int, substring(@str, n.n, 1)), 
        [weight] = w.weights,
        [digit x weight] = convert(int, substring(@str, n.n, 1)) * w.weights,
        s.d1, s.d2

Answer 2

This worked for me. If there is any better solution please share.
I am calling another function that adds no splits in to the main function.

BEGIN DECLARE @ @iValidation_Value INT，@A jcursor，jcursor INT，jcursor INT，@isum INT --@jcursor INT

-- for 7
   SELECT @iCursor = 1
   SELECT @iValidation_Value = 0
   SELECT @isum = 0
   --SELECT @jcursor = 1

   WHILE @iCursor <= @p_iStr_Len
      BEGIN
         SELECT @cCursor_Value = CONVERT(INT, SUBSTRING(REPLACE(REPLACE(@p_cValidation_Str,'M','4'),'m','4'), @iCursor, 1))
         --SELECT @cCursor_Value = CONVERT(INT, SUBSTRING(REPLACE(@p_cValidation_Str,'M','4'), @iCursor, 1))
         SELECT @iValidation_Value = (@cCursor_Value * 7)
         SELECT @iCursor = @iCursor + 4

       --WHILE @jcursor <= datalength(@iValidation_Value)
              select @isum= @isum + [frdmrpt].[FN_Sumofdigits](@iValidation_Value)
       --     BEGIN SELECT @isum = @isum + CONVERT(INT,SUBSTRING(CONVERT(VARCHAR,@iValidation_Value),@jcursor,1))
       --     SET @jcursor = @jcursor+1
       --END

      END

--for 5

   SELECT @iCursor = 2
   SELECT @iValidation_Value = 0
   --SELECT @jcursor = 1

   WHILE @iCursor <= @p_iStr_Len
      BEGIN
         SELECT @cCursor_Value = CONVERT(INT, SUBSTRING(@p_cValidation_Str, @iCursor, 1))
         SELECT @iValidation_Value = (@cCursor_Value * 5)
         SELECT @iCursor = @iCursor + 4
         SELECT @isum= @isum + [frdmrpt].[FN_Sumofdigits](@iValidation_Value)

      END


--for 3
   SELECT @iCursor = 3
   SELECT @iValidation_Value = 0

   WHILE @iCursor <= @p_iStr_Len
      BEGIN
         SELECT @cCursor_Value = CONVERT(INT, SUBSTRING(@p_cValidation_Str, @iCursor, 1))
         SELECT @iValidation_Value = (@cCursor_Value * 3)
         SELECT @iCursor = @iCursor + 4
         SELECT @isum= @isum + [frdmrpt].[FN_Sumofdigits](@iValidation_Value)

      END



--for 2   
   SELECT @iCursor = 4
   SELECT @iValidation_Value = 0

   WHILE @iCursor <= @p_iStr_Len
      BEGIN
         SELECT @cCursor_Value = CONVERT(INT, SUBSTRING(@p_cValidation_Str, @iCursor, 1))
         SELECT @iValidation_Value = (@cCursor_Value * 2)
         SELECT @iCursor = @iCursor + 4
         SELECT @isum= @isum + [frdmrpt].[FN_Sumofdigits](@iValidation_Value)

      END



--RETURN ( @isum)


      IF @isum % 10 = 0
         SELECT @isum = 0
      ELSE
         SELECT @isum = 10 - (@isum % 10)

      RETURN (Replace(@p_cValidation_Str,'M',4) + Convert(char(1), @isum))
   END