Validate Saudi identity card number

validate_saudi_id.sql

#
# validate_saudi_id() function for MySQL/MariaDB
# This function will validate Saudi identity card number
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# by Daif Alazmi (http://daif.net)
# [email protected]
# 2020-10-09
#

DROP FUNCTION IF EXISTS validate_saudi_id;
DELIMITER //

CREATE FUNCTION validate_saudi_id(idno VARCHAR(10)) RETURNS INT
BEGIN
    DECLARE counter INT(1) DEFAULT 0;
    DECLARE odd VARCHAR(2) DEFAULT 0;
    DECLARE sum INT(9) DEFAULT 0;
    DECLARE id_type VARCHAR(1) DEFAULT 0;

    IF LENGTH(idno) != 10 THEN
        RETURN -1;
    END IF;

    SET id_type = SUBSTRING(idno, 1, 1);

    IF id_type != '1' AND id_type != '2' THEN
        RETURN -1;
    end IF;

    WHILE counter < 10 DO
        IF counter % 2 = 0 THEN
            SET odd = SUBSTRING(idno, counter+1, 1);
            SET odd = CAST(odd AS SIGNED)*2;
            SET odd = LPAD(odd, 2, '0');
            SET sum = sum + CAST(SUBSTRING(odd, 1, 1) AS SIGNED) + CAST(SUBSTRING(odd, 2, 1) AS SIGNED);
        ELSE
            SET sum = sum + CAST(SUBSTRING(idno, counter+1, 1) AS SIGNED);
        END IF;

        SET counter = counter + 1;
    END WHILE;

    IF sum % 10 != 0 THEN
        return -1;
    ELSE
        return CAST(id_type AS SIGNED);
    end IF;
END;
//