How To Replace Part Of String In Sql

i have URL in db column 'guid' http://example.com/wp-content/uploads/2014/03/Waterproofing2.png i need to change it to http://example.com/blog/wp-content/uploads/2014/03/Waterproo

Solution 1:

Heya easiest way to do that is using replace function in sql

simple

REPLACE(YourString, ‘textto replace’, ‘replace withtext’)

REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

In SQL, wildcard characters are used with the SQL LIKE operator.

SQL wildcards are used to search for data within a table.

With SQL, some of the wildcards are:

Wildcard    Description
%   A substitute for zero or more characters
_   A substitute for a single character

so Quickest Way Use CONCAT: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

So an example from a friend , change the following

styles/default/xenmoods/pants.png

styles/default/xenmoods/andrew.png

styles/default/xenmoods/rawr.png

to this

http://cdn.sociallyuncensored.com/styles/default/xenmoods/pants.png
http://cdn.sociallyuncensored.com/styles/default/xenmoods/andrew.png
http://cdn.sociallyuncensored.com/styles/default/xenmoods/rawr.png

CODE:

UPDATE YOURTABLE SET path =CONCAT('http://example.com/blog/wpcontent/uploads/', path) ... where ..etc

Solution 2:

Use the replace function:

update `table` set `column`= replace (`column`, 'http://example.com/','http://example.com/blog/') where `column`like'http://example.com/blog/wp-content/uploads%'

Solution 3:

I always use this query for WordPress database moves

UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name ='home'OR option_name ='siteurl';
UPDATE wp_posts SET guid = REPLACE (guid, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://olddomain.com', 'src="http://newdomain.com');
UPDATE wp_posts SET guid = REPLACE (guid, 'http://olddomain.com', 'http://newdomain.com') WHERE post_type ='attachment';
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://olddomain.com','http://newdomain.com');