Finding and replacing strings in record MySql

Question

I have one data sets with inconsistent formatting of text data

One format shows data like:

Red Socks (American)
Brewers (American)
etc

The other format shows

Red Socks - American
Brewers - American
etc

I need to clean up one set so that it matches the other set.

I would like to move all the Brewers - American format

I am having trouble writing a query to change " (" to " - " And deleting any occurrence of "("

sgeddes · Accepted Answer · 2015-12-08 20:04:53Z

2

You can use replace:

update data set field = replace(replace(field, ' (', ' - '), ')', '')

answered Dec 8, 2015 at 20:04

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerry · Accepted Answer · 2015-12-08 20:10:33Z

1

UPDATE testing SET name = REPLACE (name, '(', '- '), name = REPLACE (name, ')', '') WHERE name LIKE '%(%'

Hey, I think this should solve your problem, where 'testing' is the name of table and 'name' is the name of the column.

answered Dec 8, 2015 at 20:10

Jerry

613 bronze badges