Skip to content

Instantly share code, notes, and snippets.

@thomedes thomedes/cif.sql
Last active Jan 3, 2016

Embed
What would you like to do?
Calculo de la letra del DNI con MySQL
DELIMITER $$
DROP FUNCTION IF EXISTS `cif_letra` $$
CREATE FUNCTION `cif_letra` (`p_cif` INT UNSIGNED) RETURNS CHAR(1)
-- ----------------------------------------------------------------------------
-- Calcula la letra que corresponde al núm. de CIF dado
-- ----------------------------------------------------------------------------
DETERMINISTIC
NO SQL
BEGIN
RETURN SUBSTR("TRWAGMYFPDXBNJZSQVHLCKE", MOD(`p_cif`, 23) + 1, 1);
END $$
DROP PROCEDURE IF EXISTS `cif_valida` $$
CREATE PROCEDURE `cif_valida` (IN `p_cif` BINARY(9))
-- ----------------------------------------------------------------------------
-- Comprueba la validez de un DNI/NIE
--
-- Solo admite la forma *canónica*.
-- Ej. "12345678Z" => OK
-- "12345678z" => MAL
-- "12.345.678-Z" => MAL
--
-- Provoca un error si no es válido
-- No hace nada si es NULL (se supone que fallará el INSERT/UPDATE)
-- ----------------------------------------------------------------------------
DETERMINISTIC
NO SQL
BEGIN
DECLARE ERR_CIF_INVALIDO CONDITION FOR SQLSTATE '45000';
SET @ok = TRUE;
SET @msg = 'La letra no corresponde al CIF dado';
IF `p_cif` REGEXP '^[0-9]{8}[TRWAGMYFPDXBNJZSQVHLCKE]$' THEN
-- Es un DNI
SET @ok := SUBSTR(`p_cif`, 9, 1)
= cif_letra(CAST(SUBSTR(`p_cif`, 1, 8) AS UNSIGNED));
ELSEIF `p_cif` REGEXP '^[KLMXYZ][0-9]{7}[TRWAGMYFPDXBNJZSQVHLCKE]$' THEN
-- Es un NIF K, NIF L, NIF M, NIE X, NIE Y o NIE Z
SET @ok := SUBSTR(`p_cif`, 9, 1)
= cif_letra(CAST(SUBSTR(`p_cif`, 2, 7) AS UNSIGNED));
ELSE
SET @ok := `p_cif` IS NULL;
SET @msg := 'Formato de CIF no válido';
END IF;
IF NOT @ok THEN
SIGNAL ERR_CIF_INVALIDO SET MESSAGE_TEXT = @msg;
END IF;
END $$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.