I have thousands of records with code in a very peculiar format that identifies boxes on street poles.
Eg: 21.305-005 / 100-A
However, this code is currently in the format 21305005100-A. As the position of the punctuation is fixed, standard of ZIP code, I am trying to put them however I do not know any function that does this.
I know I could do this with relative ease in php, but I would like to learn straight from an update in mysql.
The ideal is to store only the code and leave the formatting for the frontend, for performance and modeling reasons.
To perform this operation, you can use the combination of the concat and substr or substring functions as below:
UPDATE teste
SET numeros = CONCAT(SUBSTR(numeros,1,2), '.',SUBSTR(numeros,3,3),'-',SUBSTR(numeros,6,3),'/',SUBSTR(numeros,9,5));
Before doing a update I directly like to make a select to check how the update result will be, so I ran a test on SQLTest .
CREATE TABLE poste (
numero VARCHAR(30) NOT NULL
);
INSERT INTO poste
VALUES
('21305005100-A');
And the query:
select concat(substr(numero,1,2),'.',
substr(numero,3,3),'-',
substr(numero,6,3),'/',
substr(numero,9,5))
from poste;
Having the expected result, then I make the update :
UPDATE poste
SET numero = concat(substr(numero,1,2),'.',
substr(numero,3,3),'-',
substr(numero,6,3),'/',
substr(numero,9,5))