mysql 电话格式,MySQL输出屏蔽（即电话号码，SSN等显示格式）

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?

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 ;

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,'(###) ###-####') |

+----------------------------------+

| (012) 345-6789 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select mask(123456789,'###-#!##@(###)');

+----------------------------------+

| mask(123456789,'###-#!##@(###)') |

+----------------------------------+

| 123-4!56@(789) |

+----------------------------------+

1 row in set (0.00 sec)