CODEWITHSUNDEEP
sqlmysqlwordpressreplace
GreatestSwordsman
GreatestSwordsman

Reputation: 1185

SQL Find and Replace

How can i do the following :

In my Wordpress website, i want to replace something in the post content

All instances of : http://play.videolog.tv/videos/xx/xx/yyyyyy.flv

Replace with : [videolog]yyyyyy[/videolog]

xx can be 2 or 3 digits, but yyyyyy is always 6 numbers.

Example :

http://play.videolog.tv/videos/93/80/398682.flv to [videolog]398682[/videolog]

Upvotes: 1

Views: 207

Answers (2)

TehShrike
TehShrike

Reputation: 10084

It takes some string magic to do this entirely in MySQL. I believe this should work:

UPDATE posts_table
SET posts_table.post_text = REPLACE(posts_table.post_text, @full_url := MID(posts_table.post_text, @url_start := LOCATE('http://play.videolog.tv/videos/', posts_table.post_text), LOCATE('.flv', posts_table.post_text, @url_start) + 4 - @url_start), 
CONCAT('[videolog]', MID(@full_url, LENGTH(@full_url) - LOCATE('/', REVERSE(@full_url)) + 2, 6), '[/videolog]'))
WHERE LOCATE('http://play.videolog.tv/videos/', posts_table.post_text)

You can test it with this query:

SELECT REPLACE(posts_table.post_text, @full_url := MID(posts_table.post_text, @url_start := LOCATE('http://play.videolog.tv/videos/', posts_table.post_text), LOCATE('.flv', posts_table.post_text, @url_start) + 4 - @url_start), 
CONCAT('[videolog]', MID(@full_url, LENGTH(@full_url) - LOCATE('/', REVERSE(@full_url)) + 2, 6), '[/videolog]')) AS `result`
FROM posts_table
WHERE LOCATE('http://play.videolog.tv/videos/', posts_table.post_text)

Just replace posts_table and post_text with the correct table/column names.

This query should replace all instances of that URL, even if it is in the middle of a bunch of text.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

Update:

update MyTable
set MyColumn = concat('[videolog]', replace(SUBSTRING(SUBSTRING_INDEX(MyColumn, '/', -1), 2), '.flv', '[/videolog]'))
where MyColumn like 'http://play.videolog.tv/videos/%'

Upvotes: 3

Related Questions