Solved

MYSQL - Remove variable substrings from fields using REPLACE?

Posted on 2008-09-29
3
625 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
3 Comments
 
LVL 2

Accepted Solution

by:
Deepika_Rastogi earned 125 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 125 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now