Solved

Remove substring with parentheses in MySQL database

Posted on 2009-03-31
7
637 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
  • 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
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…

920 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

16 Experts available now in Live!

Get 1:1 Help Now