Check digit MSSQL function: remove letters-CodePudding

A simple check digit function. For example, when digit to be checked is "123456789012", the result is "8".

However, some results in the column contain letters too. For example, when the result is "abc123456789012", it should remove the "abc", and just calculate the numbers.

How can I do that in the same function?

CREATE OR ALTER FUNCTION dbo.CheckdigitVoucher
(
  @ACode AS VARCHAR(12)
)
RETURNS INTEGER
AS BEGIN

  RETURN (10 - (CAST(SUBSTRING(@ACode, 1, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 2, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 3, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 4, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 5, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 6, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 7, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 8, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 9, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 10, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 11, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 12, 1) AS INTEGER)
               )
         )
END;

CodePudding user response：

Maybe something like below can help and here's a demo link

we get all valid numbers in the digits cte, and explode out each valid character from input string into length cte followed by joining back all characters in the output cte

DECLARE @ACode  nvarchar(max)='abc123456789012'
; with digits as 
(
select 0 as valid, cast('0' as nvarchar) as v
union all
select c.valid 1 as valid, cast(c.valid 1 as nvarchar) as v  from digits c
where c.valid <9)
, length as 
(
select 0 as l,cast('' as nvarchar) as s 
union all
select c.l 1, coalesce(a.v,'') as s  from length c
outer apply 
(select v from digits where v =SUBSTRING(@Acode,l 1,1))a
where c.l <LEN(@Acode))
, output as
(
select 0 as p, cast('' as nvarchar(max)) as str
union all
select p 1 as p, concat(str,s) as str from length l join output o on o.p=l.l

)

select @ACode =str from output
where p = (select max(p) from output)

SELECT (10 - (CAST(SUBSTRING(@ACode, 1, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 2, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 3, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 4, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 5, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 6, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 7, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 8, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 9, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 10, 1) AS INTEGER)
                  CAST(SUBSTRING(@ACode, 11, 1) AS INTEGER)
                  3* CAST(SUBSTRING(@ACode, 12, 1) AS INTEGER)
               )
         )

CodePudding user response：

We can create a function which loops through the string passed in parameter, checks whether each character is a digit, and adds it to the variable to be returned if it is.

CREATE FUNCTION dbo.checkDigit (@rawValue NVARCHAR(100))
RETURNS INT
AS
BEGIN
DECLARE @i int = 0
DECLARE @res int = 0
DECLARE @this char(1)
WHILE @i < LEN(@rawValue)
  BEGIN
    SET @i = @i   1;
    SET @this = SUBSTRING(@rawValue,@i,1);
    SET @res = @res   CASE  WHEN @this LIKE '[0-9]' THEN @this ELSE 0 END
  END
SET @res = @res % 10
return @res
END
GO

✓

SELECT dbo.checkDigit('abc123456789012');
GO

| (No column name) |
| ---------------: |
|                8 |

db<>fiddle here

CodePudding user response：

You could re-write the function as a table-valued function like this:

CREATE OR ALTER FUNCTION dbo.GetVoucherCheckDigits
(
  @ACode varchar(20)
)
RETURNS table WITH SCHEMABINDING
AS
  RETURN 
  (
      WITH n(n, s) AS 
      (
        SELECT 1,   SUBSTRING(@ACode, 1,   1) UNION ALL
        SELECT n 1, SUBSTRING(@ACode, n 1, 1) FROM n 
        WHERE n < LEN(@ACode)
      ),
      s(n,s) AS 
      (
        SELECT n = ROW_NUMBER() OVER (ORDER BY n),s
        FROM n WHERE TRY_CONVERT(tinyint, s) IS NOT NULL
      )
      SELECT CheckDigit = (10 - 
        SUM(s*CASE n%2 WHEN 0 THEN 3 ELSE 1 END)) FROM s
  );

Example db<>fiddle

This is much more efficient than a scalar UDF, removes the duplication of all those 3* clauses, and allows you to handle non-numeric data all in one shot.

But really, you should fix the data, and add a constraint (e.g. CHECK (col NOT LIKE '%[^0-9]%') to prevent future bad data from getting in...