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
Solved

Remove substring with parentheses in MySQL database

Posted on 2009-03-31
7
639 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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