[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MYSQL - Remove variable substrings from fields using REPLACE?

Posted on 2008-09-29
3
Medium Priority
?
640 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

649 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