MySQL output masking (i.e. phone number, SSN, etc. display formatting)

Question

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?

Answer 1

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)

Answer 2

Let's say your People table has namelast, namefirst, and phone fields.

SELECT p.namelast, p.namefirst,     
  CONCAT(SUBSTR(p.phone,1,3), '-', SUBSTR(p.phone,4,3), '-', SUBSTR(p.phone,7,4)) AS Telephone, 
FROM People p

The CONCAT(SUBSTR()) uses the field name for the first position, character position for the second, and number of digits for the third. Using "AS Telephone" keeps your column heading clean in your query output.

One note of caution: I'd recommend against using integers for phone numbers, social security numbers, etc. The reason is that you'll probably never perform numeric computations with these numbers. While there is the odd chance you'd use them as a primary key, there's always the possibility of losing digits from numbers with leading zeros.