[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

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?
0
sdg_seattle
Asked:
sdg_seattle
2 Solutions
 
Deepika_RastogiCommented:
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
 
ursangelCommented:
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
 
sdg_seattleAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now