1

Is there a way to replace all characters (or set of characters) from a whole table? (not only inside one column)

For example - if I want to replace whole table and character "ab" inside all columns to some other (e.g. "cd") for ALL columns at once - how to do that?

Not related to specific DB technology but if it will help - let's say it is MySql.

I can create a temp table or a 'final' table if that will help..

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Joe
  • 11,983
  • 31
  • 109
  • 183

1 Answers1

1

The way to do it with MySQL is:

UPDATE MyTable
SET col001 = REPLACE(col1, 'ab', 'cd'),
    col002 = REPLACE(col2, 'ab', 'cd'),
    ...
    col100 = REPLACE(col100, 'ab', 'cd');

It's tedious to write out 100 clauses like that, but you can use SQL to generate the whole UPDATE statement:

SELECT CONCAT('UPDATE MyTable SET ',
  GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '` = REPLACE(`', COLUMN_NAME, '`, '''ab'', ''cd'')'),
  ';') AS _sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable';

(untested)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828