Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remove substring with parentheses in MySQL database

Posted on 2009-03-31
7
Medium Priority
?
652 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 5

Accepted Solution

by:
Flembosa earned 1500 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 1500 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

864 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