asked on

MYSQL - Remove variable substrings from fields using REPLACE?

I have a column with organization names that includes a year in parentheses at the end. I need to remove the year and the surrounding parentheses for each record. The year is variable. Here is an example:

Department of Housing (1993)

I want the field to be simply:

Department of Housing

I have tried a number of combinations of REPLACE, CONCAT, and LIKE but can't get anything to work. Can you provide a same UPDATE command that will accomplish this?

ASKER CERTIFIED SOLUTION

Deepika_Rastogi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ursangel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdg_seattle

ASKER

Deepika_Rastogi & ursangel,

Both solutions are interesting and useful for their own reasons. Deepika_Rastogi's one-liner is appealing for it's simplicity while ursangel's solutions reminds of what I'm going to need to do in the future with more complete replacements in which the case statement will be a good fit. Ironically, both solutions reference the CHARINDEX() function which is not supported in MYSQL; instead, I had to substitue the LOCATE() function. No big deal but if I were more of a newby I might not have figured that out.