CODEWITHSUNDEEP
mysqlsql
StevenPHP
StevenPHP

Reputation: 3597

String replace within SQL statement

I wish to replace all occurrences of ? with a single empty space within a table for all columns.

Example:

This is a?string

would become

This is a string

Currently I have found out how to do it for one column only

UPDATE tableName
   SET columnName = REPLACE(columnName,'"','\'')

Is there a way to select and apply to al columns? I don't wish to 'try' it as if the table becomes corrupted or deleted it will cause a lot of upset. Any help would be greatly appreciated.

Upvotes: 0

Views: 104

Answers (3)

Tom
Tom

Reputation: 6663

Here's a shortcut to create an update statement for all the fields in a table. You would fill in the table name and database name in the WHERE clause of the SQL statements below, then run the code and it will return a SQL statement that you could copy and then run.

For a separate update statement for each field in a table:

SELECT concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ', ''?'', '''')')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = 'table_name' AND TABLE_SCHEMA = 'database_name';

For a single update statement for every field in a table. Put UPDATE table_name then paste the results of this SQL statement.

SELECT concat('SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ', ''?'', ''''),')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = 'table_name' AND TABLE_SCHEMA = 'database_name';

If you just wanted to do update char or varchar fields you could add this to your WHERE clause:

 AND DATA_TYPE in ('char', 'varchar')

To do it for every table in a database, simply drop the TABLE_NAME logic from your WHERE clause.

Upvotes: 0

gvee
gvee

Reputation: 17161

Try before you buy:

SELECT Replace(columnName1, '?', ' ')
     , Replace(columnName2, '?', ' ')
     , Replace(columnName3, '?', ' ')
FROM   your_table

Then update if you're happy with the results

UPDATE your_table
SET    columnName1 = Replace(columnName1, '?', ' ')
     , columnName2 = Replace(columnName2, '?', ' ')
     , columnName3 = Replace(columnName3, '?', ' ')

Update

You may wish to limit the effect of your query. At current it will apply to every row in your table, regardless of whether any question marks exist in the column values or not.

Therefore you should consider adding a WHERE clause that checks for the existence.

SELECT Replace(columnName1, '?', ' ')
FROM   your_table
WHERE  Locate('?', columnName1) > 0

Upvotes: 3

Aleks G
Aleks G

Reputation: 57306

You cannot do it for all columns automatically, you'll need to list the columns individually, but you can still do it with one statement:

UPDATE tableName
   SET columnName1 = REPLACE(columnName1,'"','\''),
       columnName2 = REPLACE(columnName2,'"','\''),
       columnName3 = REPLACE(columnName3,'"','\''),
       ...
       columnNameN = REPLACE(columnNameN,'"','\'')

Upvotes: 3

Related Questions