Solved

Remove substring with parentheses in MySQL database

Posted on 2009-03-31
7
643 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 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