Filter String field snippet in Mysql

Asked

Viewed 2,139 times

2

I’m having a hard time getting just the zip code of a field where address data is not atomic, would like to know a way using Mysql to get only the 8 numeric values of the ZIP code snippet from the field of all base records that contain 8 successive digits and if possible considering the cases where there is a tab hyphen (-) but bringing only the 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 database regex

  • Regex will not be the solution, since in Mysql it only serves to "match" field with a certain value, not to extract that value from the field. If it was only from Brazil until it was possible, but with this data, I do not know.

  • I only need the Brazilian zip code yes! 8 digits.

  • You select normally, and then just cut the string programmatically.

  • It has to be done on Workbench, only using SQL.

2 answers

2


Considering only the Brazilian ZIP codes, as you said in the comments, you can use operations on strings to extract the numerical part, and REGEXP to filter results that do not contain Brazilian Zip Code:

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

http://sqlfiddle.com/#! 2/1e1e3/5

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

  • Thanks, I suspected that using multiple functions and regular expression would give.

0

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

Browser other questions tagged mysql sql database regex

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