Format ZIP in Query - Mysql

Asked

Viewed 1,020 times

4

In my column of the bank I have the field CEP varchar(20), and a record: "92820142" there is some way to format in the pattern: 92,820-142 during the consultation?

I tried using the format, but it conflicted with the data type.

mysql

  • Usually this is dealt with in the presentation layer. Are you sure you want to deal with the query level?

  • 2

    Yes Jefferson, it’s purposeful, a college job

2 answers

5


Use this function:

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 ;

How to use:

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)

Source: https://stackoverflow.com/questions/10112718/mysql-output-masking-i-e-phone-number-ssn-etc-display-formatting

1

The simplest form I believe is using CONCAT() together with SUBSTRING:

SELECT CONCAT(SUBSTRING(CEP,1,2),'.',SUBSTRING(CEP,3,3),'-',SUBSTRING(CEP,6,3)) FROM {suatabela} WHERE ...

It will work if everyone is in the 8-digit pattern with no other character, such as "." or "-".

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.