replace some text in a mysql column

Question

given those strings in a mysql column...

All of the above  (-2/2)
All of the above  (0/3)
Once or twice  (1/3)

is it possible to do a replace in order to delete the brackets and their content?

thanks

Do you store exactly those strings, or is the (n/n) variable? — Michael Berkowski
– Michael Berkowski, Commented Feb 14, 2014 at 15:50
But otherwise, the text is either All of the above or Once or twice? And you want the end result to strip off the (n/n) entirely, resulting in All of the above or Once or twice? — Michael Berkowski
– Michael Berkowski, Commented Feb 14, 2014 at 15:52
the text is variable and the content inside the brackets is variable as well I need to remove all brackets and content — user3174311
– user3174311, Commented Feb 14, 2014 at 15:56
Still not clear - I thought it was but your last comment confuses me a little. Please edit your question post a sample of existing rows, and a corresponding sample of what the expected output looks like. MySQL does not have native regular expression replacements, unfortunately, but this may be doable with regular string operations. — Michael Berkowski
– Michael Berkowski, Commented Feb 14, 2014 at 16:00

mamdouh alramadan · Accepted Answer · 2014-02-14 16:13:49Z

1

here you go:

create table rep(
sometext text
);
insert into rep(sometext) values('All of the above  (-2/2)'),
('All of the above  (0/3)'),
('Once or twice  (1/3)');
update rep set sometext = replace(sometext,substring(sometext,locate('(',sometext)),'');

I used replace, then I found the substring of the text using locate, to send it as second arg to replace.

References: Locate , substring , replace

FIDDLE.

edited Feb 14, 2014 at 16:13

answered Feb 14, 2014 at 16:05

mamdouh alramadan

8,5166 gold badges39 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Berkowski Over a year ago

Probably wrap that in RTRIM() as well.

user3174311 Over a year ago

Thanks for your reply. I thins this is not exactly what I need, because if I have this row "All of the above (some text) bla bla (1/2)" I should get "All of the above (some text) bla bla" anyway thanks for your help

mamdouh alramadan Over a year ago

@user3174311 - which row do you mean?!

user3174311 Over a year ago

basically, I need to replace "/\s(\d\/\d)/" with an empty string

mamdouh alramadan Over a year ago

ok, i don't see the problem here?! the only thing you are missing is the space which as Michael mentioned you can wrap the update with RTRIM to solve it!!

Collectives™ on Stack Overflow

replace some text in a mysql column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related