Solved

MYSQL - Remove variable substrings from fields using REPLACE?

Posted on 2008-09-29
3
623 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

18 Experts available now in Live!

Get 1:1 Help Now