CODEWITHSUNDEEP
sqlmysqlstring
Moak
Moak

Reputation: 12885

Extract data from one field into another in mysql

I have an old table which has a column like this

1 | McDonalds (Main Street)
2 | McDonalds (1st Ave)
3 | The Goose
4 | BurgerKing (Central Gardes)
...

I want to match the venues like ' %(%)' and then extract the content in the brackets to a second field to result in

1 | McDonalds | Main Street
2 | McDonalds | 1st Ave
3 | The Goose | NULL
4 | BurgerKing| Central Gardes
...

How would one go about this?

Upvotes: 0

Views: 324

Answers (2)

Moak
Moak

Reputation: 12885

I installed these user defined functions

http://www.mysqludf.org/lib_mysqludf_preg/

Then I could select the "branches" via 

SELECT `id`, `name`, preg_capture('/.*?\\((.*)\\)/',`name`,1) AS branch FROM `venues`

Upvotes: 0

Oswald
Oswald

Reputation: 31675

MySQL provides string functions for finding characters and extracting substrings. You can also use control flow functions to handle the cases where the venue is not present.

Upvotes: 1

Related Questions