Filter string field snippet in MySQL

Question

Filter string field snippet in MySQL

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I'm having difficulty getting only the zip code of a field in which the address data is not atomic, I'd like to know a way using MySQL to get only the 8 numeric values of the ZIP code from the field of all records in the database which contain 8 successive digits and if possible considering the cases where there is a hyphen (-) but only bringing numbers as a result of the selection.

Typical values in the table of interest field: (address field data)

BR-50670901 Recife, PE, Brazil.

BR-70910900 Brasilia, DF, Brazil.

BR-14040901 Ribeirao Preto, SP, Brazil.

BR-74001970 Goiania, Go, Brazil.

Nucleo Fis Aplicada, BR-70919970 Brasilia, DF, Brazil.

F-35042 Rennes, France.

Dept Matemat, BR-13083970 Campinas, SP, Brazil.

Rochester, NY 14627 USA.

Cambridge CB3 9EW, England.

mysql sql regex

asked by anonymous 07.05.2014 / 03:15

2 answers

0

MySQL does not currently allow REGEX in REPLACE. A different way of doing this is to bring the database data to another programming language, for example PHP, perform the extraction operations and return the database with UPDATE.

07.05.2014 / 03:41

Error trying to connect to the database Problem importing from User models

score 2 · Accepted Answer

Considering only the Brazilian CEPs, as you said in the comments, you can use operations on strings to extract the numeric part, and REGEXP to filter results that do not contain CEPs from Brazil:

SELECT LEFT(SUBSTRING_INDEX(endereco, 'BR-', -1), 8) AS CEP
FROM enderecos
WHERE endereco REGEXP 'BR-[0-9]{8}'

link

SUBSTRING_INDEX(endereco, 'BR-', -1) takes everything to the right of the first occurrence of "BR-". The LEFT isolates the first 8 characters of this substring.