Solved

Remove substring with parentheses in MySQL database

Posted on 2009-03-31
7
641 Views
Last Modified: 2012-08-14
In a table called catalog, I have a column called SongTitle with song names...some of which contain parentheses with extra name information about the title. I need to remove info in the parentheses and the surrounding parentheses for each record.  Here is an example:

Photograph (Pop Radio Edit)

I want the field to be:

Photograph

Ideally, I first want to copy the whole field to another column in the catalog table called AkaSongTitle and then do the replace on the SongTitle column.

I have tried a number of combinations but can't seem to make it work. Can you please help?




0
Comment
Question by:jahwalk
[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
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:Flembosa
ID: 24030418
Declare @test as nvarchar(55)

SET @test = 'Photograph (Pop Radio Edit)'

SELECT  rtrim(ltrim(replace(@test, substring(@test, charindex('(', @test), charindex(')', @test) - charindex('(', @test) + 1), '')))
0
 

Author Comment

by:jahwalk
ID: 24030985
Thanks for your help Flembosa. I am getting an error when I try to run your query.
------
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Declare @test as nvarchar(55)

SET @test = 'Photograph (Pop Radio Edit)'

SE' at line 1
---------

I'm new to MySQL so I am sure it is something I am not doing right. Can you explain in a bit more detail what my issue is and how I can resolve it. Many thanks.
0
 

Author Comment

by:jahwalk
ID: 24031708
The parentheses at nvarchar(55) are highlighted red so it is causing the syntax error to come up. In fact all the parentheses in the query are highlighted red. Is this right or do I need to do something else with this query? Thanks for your help. I am stuck.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 5

Accepted Solution

by:
Flembosa earned 500 total points
ID: 24031910
jahwalk,

I'm not sure what the difference is between MySQL and SQL 2005, so I'm sorry about the syntax issue. The function charindex([search character], [search string]) will return the index the character is found in your string. An example of this would be:
SELECT charindex('(', 'Photograph (Pop Radio Edit)')
Results: 27
The function substring([string], [start index], [end index]) will return part of the string. An example of this would be:
SELECT substring('Photograph (Pop Radio Edit)', 2, 3)
Results: hot

If all of your text is before you first parentheses then you could use:
SELECT LTRIM(LEFT([field name], charindex('(', [field name]) - 1))

Flembosa
0
 

Author Comment

by:jahwalk
ID: 24032345
I still don't have this working yet. I've tried various things but still no solution. I can't rely on taking out everything from the first parentheses.

This code will remove the parentheses but not the information in the parentheses. I'm not sure how to update this to remove the text inside the parentheses as well. Does this help or provide any additional ideas?

UPDATE
        yourTable
  SET
        yourColumn = LEFT( RIGHT( yourColumn, LENGTH( yourColumn ) - 1 ), LENGTH( yourColumn ) - 2 )
  WHERE
        yourColumn REGEXP "^\\(.*\\)$"

0
 
LVL 5

Assisted Solution

by:Flembosa
Flembosa earned 500 total points
ID: 24032472
Try this...

SELECT REPLACE([fieldname], SUBSTRING([fieldname], instr('(', [fieldname]), instr(')', [fieldname])), '')
0
 

Author Comment

by:jahwalk
ID: 24197702
Flembosa,

Sorry for the delay. I still couldn't get the last example you gave me to work. I decided to go with:

SELECT LTRIM(LEFT([field name], charindex('(', [field name]) - 1))

except the charindex wasn't working for MySQL. I changed this to locate. Here is the working code.

UPDATE
your table
SET
[field name] = LTRIM(LEFT([field name], locate('(', [songtitle]) - 1))

Thanks for your help. I wish I could get the other to work. Where it will go through an entire column (field) of data and remove just the parentheses and the data inside the parentheses.


0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

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…
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've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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