How to search and replace strings with SQL

Question

so i´ve change my mind....

i´ve enter around 1200 records into a text type field... like so:

this is <span class="italic">the</span> flower<br />
that speaks the mind<br />
bla bla bla<br />

If you notice, i've inserted a class named italic... well that is my mistake... I need to loop all of the 1200 records and replace the class italic with espWord

How do i achieve that ?

SELECT lyrics.lyrics_id, lyrics.lyrics
FROM lyrics
WHERE ?????

replace italic with espWord

user557846 · Accepted Answer · 2012-06-09 04:20:12Z

1

UPDATE lyrics set lyrics.lyrics = replace(lyrics.lyrics, 'italic', 'espWord');

I would never personally have a table name with a field of the same name; to great a potential for confusion.

answered Jun 9, 2012 at 4:20

user557846

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

3 Comments

flowfree Over a year ago

Note that this will replace all "italic" found in the whole text.

user557846 Over a year ago

lets hope not to may people sing of font styles :-)

Marco Over a year ago

wow! that was fast... tried it and it works... 1200 records updated in 0.016 seconds !!!

akester · Accepted Answer · 2012-06-09 04:18:01Z

0

I'm not well versed enough with MySql to say if there's a complete MySql solution, but You could try selecting the data like

SELECT SELECT lyrics.lyrics_id, lyrics.lyrics
FROM lyrics
WHERE `field` LIKE '%<span class=\"italics\">%'

Then use a program like preg_replace or sed to replace the value and update the field. I haven't used both in long enough that I can't think of the expression off the top of my head.

Hope this helps.

answered Jun 9, 2012 at 4:18

akester

631 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to search and replace strings with SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related