MySQL输出遮罩(例如电话号码，SSN等显示格式) [英] MySQL output masking (i.e. phone number, SSN, etc. display formatting)

问题描述

是否有内置的SQL函数可以屏蔽输出数据?

Is there a built-in SQL function that will mask output data?

假设我有一个整数列，代表一个电话号码(适用于任何国家).有没有比将它们细分成小串，加载散列，破折号和点然后再将它们重新串联在一起的更好的显示数字的方法了?

Let's say I have an integer column, that represents a phone number (for any country). Is there a better way to display the numbers than sub-stringing them apart, loading hashes, dashes and dot, then concatenating them back together?

我知道几种语言都有一个功能，可以在显示数据时简单地掩盖数据，而不是对其进行重组. MySQL有类似的东西吗?

I know several languages have a feature to simply mask the data as it is displayed instead of restructuring it. Does MySQL have something similar?

推荐答案

这就是我想出的，如果您有任何修改或改进，请留下它们作为注释，我将更新代码.否则，如果您喜欢它，请不要碰它.享受吧！

Here's what I came up with, if you have any modifications or improvements please leave them as comments and I will update the code. Otherwise if you like it, don't for get to bump it. Enjoy!

DELIMITER //

CREATE FUNCTION mask (unformatted_value BIGINT, format_string CHAR(32))
RETURNS CHAR(32) DETERMINISTIC

BEGIN
# Declare variables
DECLARE input_len TINYINT;
DECLARE output_len TINYINT;
DECLARE temp_char CHAR;

# Initialize variables
SET input_len = LENGTH(unformatted_value);
SET output_len = LENGTH(format_string);

# Construct formated string
WHILE ( output_len > 0 ) DO

SET temp_char = SUBSTR(format_string, output_len, 1);
IF ( temp_char = '#' ) THEN
IF ( input_len > 0 ) THEN
SET format_string = INSERT(format_string, output_len, 1, SUBSTR(unformatted_value, input_len, 1));
SET input_len = input_len - 1;
ELSE
SET format_string = INSERT(format_string, output_len, 1, '0');
END IF;
END IF;

SET output_len = output_len - 1;
END WHILE;

RETURN format_string;
END //

DELIMITER ;

使用方法...仅适用于整数(即SSN Ph#等)

Here's how to use it... It only works for integers (i.e. SSN Ph# etc.)

mysql> select mask(123456789,'###-##-####');
+-------------------------------+
| mask(123456789,'###-##-####') |
+-------------------------------+
| 123-45-6789                   |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select mask(123456789,'(###) ###-####');
+----------------------------------+
| mask(123456789,'(###) ###-####') |
+----------------------------------+
| [phone]                   |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select mask(123456789,'###-#!##@(###)');
+----------------------------------+
| mask(123456789,'###-#!##@(###)') |
+----------------------------------+
| 123-4!56@(789)                   |
+----------------------------------+
1 row in set (0.00 sec)

这篇关于MySQL输出遮罩(例如电话号码，SSN等显示格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！