Skip to content

Instantly share code, notes, and snippets.

@rodrigotxt

rodrigotxt/mask.sql

Created February 14, 2019 17:30
Show Gist options
  • Save rodrigotxt/00714b162031f44aea376060235692a2 to your computer and use it in GitHub Desktop.
Save rodrigotxt/00714b162031f44aea376060235692a2 to your computer and use it in GitHub Desktop.
Download ZIP
Mysql MASK function - format numbers
Raw
mask.sql
## Apagando a função se já existe
DROP FUNCTION IF EXISTS MASK;
## Criando a função
CREATE FUNCTION MASK(val VARCHAR(100), mask VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC
BEGIN
DECLARE maskared VARCHAR(100) DEFAULT "";
DECLARE k INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
WHILE i < CHAR_LENGTH(mask) DO
SET i = i + 1;
IF SUBSTRING(mask, i, 1) = '#' THEN
IF k < CHAR_LENGTH(val) THEN
SET k = k+1;
SET maskared = CONCAT(maskared, SUBSTRING(val, k, 1));
END IF;
ELSE
IF i < CHAR_LENGTH(mask) THEN
SET maskared = CONCAT(maskared, SUBSTRING(mask, i, 1));
END IF;
END IF;
END WHILE;
RETURN maskared;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment