Remove unwanted characters in a string using regex

Question

I have a string like this in my column:

<p>[img ret872154ftu] fileaddress [/img ret872154ftu]</p>
<p>[img fd68721cvn] fileaddress [/img fd68721cvn]</p>
<p>[img xdfh654t] fileaddress [/img xdfh654t]</p>

Now i wish to remove unwanted chars inside [img] and [/img]. I have already used this query but does not work:

UPDATE `table` SET `content` = replace(`content`, '[img [^]*]', '[img]');

Any suggestion?

REPLACE doesn't take a regular expression, and there is not a native mysql function for regex replacing, only comparing. — Anthony
– Anthony, Commented Jan 1, 2014 at 19:39

Mihai · Accepted Answer · 2014-01-01 19:48:28Z

3

UPDATE table SET
 column=REPLACE(column,SUBSTRING_INDEX(SUBSTRING_INDEX(column,'/img ',-1),']',1),'');

UPDATE table SET
 column=REPLACE(column,SUBSTRING_INDEX(SUBSTRING_INDEX(column,'[img ',-1),']',1),'');

SQL Fiddle

2 updates,with different delimiters because the texts might be different.

edited Jan 1, 2014 at 19:48

answered Jan 1, 2014 at 19:28

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Remove unwanted characters in a string using regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related