You want to do something like this<\/p>\n
update table_name set column_name = replace(column_name, ‘http:\/\/dev.DrunkOnJudgement.com’, ‘http:\/\/DrunkOnJudgement.com’);<\/p>\n
this will ensure that you simply replace the text you are looking for in a specific column with the text you want it to be without changing any text around it.<\/p>\n
so for example you could just shorten it to something like: replace(column_name, ‘dev.DrunkOnJudgment.com’, ‘DrunkOnJudgment.com’)<\/p>\n
You can also specify a where clause so that you only replace items that contain that text so something like this:<\/p>\n
where column_name like ‘%dev.DrunkOnJudgement.com%’<\/p>\n
Ok to do something like this for all columns in all tables, basically search the entire db. You can use a statement like this:<\/p>\n
SELECT Concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ',''dev.DrunkOnJudgment.com'',''DrunkOnJudgment.com'')', ' WHERE ', COLUMN_NAME, ' like ''%dev.DrunkOnJudgment.com%''' ) FROM INFORMATION_SCHEMA.COLUMNS\n<\/code><\/pre>\nit will output a sql statement like the one above for each column and table in the database and because you are using a replace statement if it does not find the text it does not replace anything it also ensure you only update records that actually contain that text.<\/p>\n
So to automate this you would use a cursor, I have not tested the following code but it would look something like this:<\/p>\n
DECLARE done BOOLEAN DEFAULT 0;\n DECLARE sql VARCHAR(2000);\n\n DECLARE cmds CURSOR\n FOR\n SELECT Concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ',''dev.DrunkOnJudgment.com'',''DrunkOnJudgment.com'')', ' WHERE ', COLUMN_NAME, ' like ''%dev.DrunkOnJudgment.com%''' ) FROM INFORMATION_SCHEMA.COLUMN;\n\n DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;\n OPEN cmds;\n REPEAT\n FETCH cmds INTO sql;\n PREPARE stmt FROM sql;\n EXECUTE stmt;\n DROP PREPARE stmt;\n UNTIL done END REPEAT;\n CLOSE cmds;\n<\/code><\/pre>\nOriginally posted 2014-02-06 04:19:48. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
You want to do something like this update table_name set column_name = replace(column_name, ‘http:\/\/dev.DrunkOnJudgement.com’, ‘http:\/\/DrunkOnJudgement.com’); this will ensure that you simply replace the text you are looking for in a specific column with the text you want it to be without changing any text around it. so for example you could just shorten it to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2692","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/2692","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=2692"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/2692\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=2692"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=2692"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=2692"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}