I have 100's of cells in our database which contain ?
instead of '
. It is possible that this might happen in all rows and columns and in more than one word per cell. Here is an example of just one cell.
Parents? CUI assumed equal to the sum of the father?s/stepfather?s and mother?s/ stepmother?s income .
I want to write an SQL statement which finds all the cells that contain ?
(may be more than one per cell) and replace them with '
. I am sure that all ?
have to be replaced without exception.
I know there is a function replace but I couldn't know how to extract a character from a string in sql.
This is one example I got but it couldn't help me.
UPDATE dbo.authors
SET city = replace(city, 'Salt', 'Olympic')
WHERE city LIKE 'Salt%';
Any ideas?
UPDATE databaseName.tableName
SET columnName = replace(columnName, '?', '''')
WHERE columnName LIKE '%?%'
This will replace all ?
with '
:
UPDATE dbo.authors
SET city = replace(city, '?', '''')
WHERE city LIKE '%?%'
If you need to update more than one column, you can either change city
each time you execute to a different column name, or list the columns like so:
UPDATE dbo.authors
SET city = replace(city, '?', '''')
,columnA = replace(columnA, '?', '''')
WHERE city LIKE '%?%'
OR columnA LIKE '%?%'
Source: Stackoverflow.com