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.
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, '?', ' ')
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
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,'"','\'')
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.
UPDATE tableName SET column1 = REPLACE(column1,'"','\''),column2 = REPLACE(column2,'"','\'') ...*toUPDATEall columns. Hopefully this question helps others out also.