sql find and replace-Collection of common programming errors

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 something like: replace(column_name, ‘dev.DrunkOnJudgment.com’, ‘DrunkOnJudgment.com’)

You can also specify a where clause so that you only replace items that contain that text so something like this:

where column_name like ‘%dev.DrunkOnJudgement.com%’

Ok to do something like this for all columns in all tables, basically search the entire db. You can use a statement like this:

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

it 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.

So to automate this you would use a cursor, I have not tested the following code but it would look something like this:

  DECLARE done BOOLEAN DEFAULT 0;
   DECLARE sql VARCHAR(2000);

   DECLARE cmds CURSOR
   FOR
   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;

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   OPEN cmds;
   REPEAT
      FETCH cmds INTO sql;
      PREPARE stmt FROM sql;
      EXECUTE stmt;
      DROP PREPARE stmt;
   UNTIL done END REPEAT;
   CLOSE cmds;