Update with character in the middle of the string

Asked

Viewed 261 times

2

I have thousands of records with a code in a very peculiar format that identifies boxes on streetlights.

Ex.: 21.305-005/100a

However, this code is currently in 21305005100-A format. As the position of the score is fixed, ZIP code, I’m trying to put them but I don’t know any function that does this.

I know I could do this with relative ease in php, but I would like to learn straight in a mysql update.

mysql

2 answers

2


Ideal is to store only the code and leave the formatting to the frontend, for performance and modeling reasons.

To perform this operation, you can use the combination of functions concat and substr or substring as below:

UPDATE teste
SET numeros = CONCAT(SUBSTR(numeros,1,2), '.',SUBSTR(numeros,3,3),'-',SUBSTR(numeros,6,3),'/',SUBSTR(numeros,9,5));

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substr https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring

  • So it did, but I think it’s substring(...)

  • In the documentation indicates the two options as valid, I will edit there in the reply and include the links to be more complete :)

1

Before making a update directly like to make a select to check how the result of update, so I did a test on Sqltest.

CREATE TABLE poste ( 
numero VARCHAR(30) NOT NULL
);

INSERT INTO poste
VALUES
  ('21305005100-A');

And the consultation:

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 do the update:

UPDATE poste
SET numero = concat(substr(numero,1,2),'.',
              substr(numero,3,3),'-',
              substr(numero,6,3),'/',
              substr(numero,9,5))

Browser other questions tagged mysql

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