Luhn 算法

代码片段

Luhn 算法

适用于 PostgreSQL

任何版本

用

SQL

依赖

无

由 Craig Ringer 用纯 SQL 实现的 Luhn 算法（PostgreSQL 函数方言，但应易于移植到其他 DBMS）。请注意，此实现纯粹是算术的；它完全避免了字符串操作。

参见：http://en.wikipedia.org/wiki/Luhn_algorithm

CREATE OR REPLACE FUNCTION luhn_verify(int8) RETURNS boolean AS $$
-- Take the sum of the
-- doubled digits and the even-numbered undoubled digits, and see if
-- the sum is evenly divisible by zero.
SELECT
         -- Doubled digits might in turn be two digits. In that case,
         -- we must add each digit individually rather than adding the
         -- doubled digit value to the sum. Ie if the original digit was
         -- `6' the doubled result was `12' and we must add `1+2' to the
         -- sum rather than `12'.
         MOD(SUM(doubled_digit / INT8 '10' + doubled_digit % INT8 '10'), 10) = 0
FROM
-- Double odd-numbered digits (counting left with
-- least significant as zero). If the doubled digits end up
-- having values
-- > 10 (ie they're two digits), add their digits together.
(SELECT
         -- Extract digit `n' counting left from least significant
         -- as zero
         MOD( ( $1::int8 / (10^n)::int8 ), 10::int8)
         -- Double odd-numbered digits
         * (MOD(n,2) + 1)
         AS doubled_digit
         FROM generate_series(0, floor(log( $1 ))::integer) AS n
) AS doubled_digits;

$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_verify(int8) IS 'Return true iff the last digit of the
input is a correct check digit for the rest of the input according to Luhn''s
algorithm.';

CREATE OR REPLACE FUNCTION luhn_generate_checkdigit(int8) RETURNS int8 AS $$
SELECT
     -- Add the digits, doubling even-numbered digits (counting left
     -- with least-significant as zero). Subtract the remainder of
     -- dividing the sum by 10 from 10, and take the remainder
     -- of dividing that by 10 in turn.
     ((INT8 '10' - SUM(doubled_digit / INT8 '10' + doubled_digit % INT8 '10') %
                       INT8 '10') % INT8 '10')::INT8
FROM (SELECT
         -- Extract digit `n' counting left from least significant\
         -- as zero
         MOD( ($1::int8 / (10^n)::int8), 10::int8 )
         -- double even-numbered digits
         * (2 - MOD(n,2))
         AS doubled_digit
         FROM generate_series(0, floor(log( $1 ))::integer) AS n
) AS doubled_digits;

$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_generate_checkdigit(int8) IS 'For the input
value, generate a check digit according to Luhn''s algorithm';

CREATE OR REPLACE FUNCTION luhn_generate(int8) RETURNS int8 AS $$
SELECT 10 * $1 + luhn_generate_checkdigit($1);
$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_generate(int8) IS 'Append a check digit generated
according to Luhn''s algorithm to the input value. The input value must be no
greater than (maxbigint/10).';

CREATE OR REPLACE FUNCTION luhn_strip(int8) RETURNS int8 AS $$
SELECT $1 / 10;
$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_strip(int8) IS 'Strip the least significant digit from
the input value. Intended for use when stripping the check digit from a number
including a Luhn''s algorithm check digit.';