How to remove text between 2 characters using query in MYSQL

Question

I have table with field called description which contains text like following

|Description                              |
|-----------------------------------------|
|This is a text <a>blah</a> <br/> <img /> |
|This is <b>second</b> <a>row</a>         |

I would like to remove everything between "<" and ">"

Required Output

|Description                              |
|-----------------------------------------|
|This is a text blah                      |
|This is second row                       |

Is there anyway I can achieve this using MYSQL query. I cant currently use Stored procedure for the same as I don't have rights. I have requested for the access to use stored procedure but that will take some time and I have to finish this bit urgently.

Thanks.

Update: my question is similar to Remove string between 2 characters from text string but I want it to be done in MYSQL query.

Both of you, regex won't do it, since it only does matching, not replacement. — slugonamission
– slugonamission, Commented Sep 11, 2012 at 16:19
@slugonamission I dont want to update my table. I just want output to be modified. — Kuldeep Daftary
– Kuldeep Daftary, Commented Sep 11, 2012 at 16:24
@KuldeepDaftary - it's still impossible, I was meaning that it can't do replacement even at the select stage. — slugonamission
– slugonamission, Commented Sep 11, 2012 at 16:29
Alright after long research I didnt really find a way to solve it using query hence used Stored Procedure. — Kuldeep Daftary
– Kuldeep Daftary, Commented Sep 17, 2012 at 10:12

slugonamission · Accepted Answer · 2012-09-11 16:18:54Z

3

In short, you can't, you need a more advanced language like PHP. Your SQL query is not the place to be doing string manipulation.

answered Sep 11, 2012 at 16:18

slugonamission

9,6741 gold badge36 silver badges41 bronze badges

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

1 Comment

slugonamission Over a year ago

You could do it using concatenation and substrings, but that would require you to know how many tags each field contained.

Jeff Maner · Accepted Answer · 2012-09-11 16:39:57Z

1

How about REPLACE() using a wildcard to capture the unknown text between opening and closing brackets? http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

answered Sep 11, 2012 at 16:39

Jeff Maner

1,1792 gold badges9 silver badges24 bronze badges

Comments

hamedkh · Accepted Answer · 2012-09-11 16:48:31Z

0

if you use php as your server side language you can use strip_tags function to remove tags.I hope it can help you.

answered Sep 11, 2012 at 16:48

hamedkh

1,0294 gold badges19 silver badges35 bronze badges

Comments

Daniel · Accepted Answer · 2016-06-17 22:06:05Z

since this question is still in top google results, maybe the answer might help someone:

a quick function that replaces the content between two characters. It should work with words as well, but I haven't test it with words. For deleting in the following string: "this is a [demo]"

the string between [ and ] do:

select deletestr("this is a [demo]" ,'[',']');

and the function:

 CREATE DEFINER=`replace_with_your_db`@`%` 
FUNCTION `deletestr` (`inputstr` TEXT CHARSET utf8, `fromstr` VARCHAR(10) CHARSET utf8, `tostr` VARCHAR(10) CHARSET utf8) RETURNS text CHARSET utf8
    NO SQL
RETURN
replace(inputstr,substr(inputstr, locate(fromstr,inputstr),locate(tostr,inputstr)-locate(fromstr,inputstr)+length(tostr)),'')

Collectives™ on Stack Overflow

How to remove text between 2 characters using query in MYSQL

Required Output

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Required Output

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related