• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 40
  • Last Modified:

Removing Brackets and the content within SQL query

I have a column in my database called Horse_Name. This currently includes the country if outside of UK in brackets (ire),(fra) ect.

Is it possible to run a query to remove the brackets and the content very much like in excel whereby you can replace all (*) within the given column?

An example would be as follows

Current                        Outcome

Horse_Name                    Horse_Name

Rocket(FRA)                  Rocket   
Apple Jade(IRE)              Apple Jade
Zac                          Zac

Open in new window

I would like to go through the whole column named Horse_Name to remove the brackets and data.

I have tried the following code but it only finds the values and does not replace them

SELECT REPLACE(Horse_Name, SUBSTRING(Horse_Name, LOCATE('(', Horse_Name), LENGTH(Horse_Name) - LOCATE(')', REVERSE(Horse_Name)) - LOCATE('(', Horse_Name) + 2), '') AS Horse_Name
FROM TFResults;
Kind Regards,

Open in new window


J
0
runnerjp2005
Asked:
runnerjp2005
  • 3
  • 3
2 Solutions
 
NorieVBA ExpertCommented:
You would need an UPDATE query for that, something like this perhaps.

UPDATE TFResults
SET Horse_Name = SUBSTRING(Horse_Name, LOCATE('(', Horse_Name), LENGTH(Horse_Name) - LOCATE(')', REVERSE(Horse_Name)) - LOCATE('(', Horse_Name) + 2), '')
0
 
runnerjp2005Author Commented:
Thanks - that does make sense but i am getting a syntax error Capture.PNG
0
 
NorieVBA ExpertCommented:
Try this.

UPDATE TFResults
SET Horse_Name = TRIM(SUBSTRING(CONCAT(Horse_Name,'('), 1, LOCATE('(', CONCAT(Horse_Name,'('))-1))
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
runnerjp2005Author Commented:
The script looks to work to an extent but now I am running into another issue

Capture.PNG
To prevent duplicate entry's I use a key which is taken from horse name and the date (A horse would not complete twice on the same day). Why would updating the field cause a duplicate entry error?
0
 
runnerjp2005Author Commented:
Thanks for your help - I have successfully ran the script!
0
 
NorieVBA ExpertCommented:
What I suggested removes everything after the first '(' rather than everything within the brackets and the brackets themselves.

I can adjust the expression to only remove the latter if that's what you really need.
0

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now