0

I would really appreciate your help on the following problem:

I have a column containing addresses (all mixed together). These addresses are quite heterogeneous, so a simple selection based on position won't do it. Thanks to helpful people on stackoverflow, I already found a regex pattern to split it up. Now the only question left is how to perform an SQL UPDATE query in order to separate the address (maybe regexp_replace() will do the job?).

current format:

Street 123
12343 City

target format:

address: street 123
zip: 12343
city: City

regex pattern:

^([a-zäöüß\s\d.,-]+?)\s*([\d\s]+(?:\s?[-|+/]\s?\d+)?\s*[a-z]?)?\s*(\d{4,5})\s*(.+)?$

So the goal is to update the columns city and zip with the relevant part of the String. The current field address should only contain the first line afterwards.

Thanks for your help!

ratio
  • 1
  • 2
  • 1
    Possible duplicate of [How to parse freeform street/postal address out of text, and into components](https://stackoverflow.com/questions/11160192/how-to-parse-freeform-street-postal-address-out-of-text-and-into-components) – ctwheels Feb 13 '18 at 19:38
  • Maybe `regexp_replace()` *would* do the job. If so, you should change to a database that supports that functionality. That is not MySQL. – Gordon Linoff Feb 13 '18 at 19:39
  • If the current format is always exactly that, then you can split with new line and then split second row at the first space to get three columns as required. – clinomaniac Feb 13 '18 at 19:49
  • AFAIK, Unlike MariaDB, MySql does not have regex_replace. See https://github.com/hholzgra/mysql-udf-regexp – Mehrad Sadegh Feb 14 '18 at 01:52
  • Some things are better done in a real programming language, not SQL. – Rick James Feb 24 '18 at 03:13

0 Answers0