?
Solved

MYSQL - Remove variable substrings from fields using REPLACE?

Posted on 2008-09-29
3
Medium Priority
?
636 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:sdg_seattle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
Deepika_Rastogi earned 500 total points
ID: 22602913
Hi

to update the given field u can try this

update [your table] set [your department field] =
              ltrim ( rtrim ( left ( [your department field] , charindex ( ' (' , [your department field] ) -1) ) )

this will remove the year and the surrounding parenthesis

Hope u find the solution.
Deepika
0
 
LVL 5

Assisted Solution

by:ursangel
ursangel earned 500 total points
ID: 22602919
select case charindex('(', Name)
      when 0 then Name
      else
                         substring(Name, 1, (charindex('(', Name)) -1 )
      end Usr_name
 from users

This will give you the exact Name removing the parentheses.
Use this case statement in your where clause. I have used a case , to be on the safer side, if the NAme does not contains a parentheses.
If you are sure about the table value, you can directly use the ELSE part alone.
0
 

Author Closing Comment

by:sdg_seattle
ID: 31501448
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.  
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question