How to replace a character from a String in SQL?

Question

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?

Answer 1

Are you sure that the data stored in the database is actually a question mark? I would tend to suspect from the sample data that the problem is one of character set conversion where ? is being used as the replacement character when the character can't be represented in the client character set. Possibly, the database is actually storing Microsoft "smart quote" characters rather than simple apostrophes.

What does the DUMP function show is actually stored in the database?

SELECT column_name,
       dump(column_name,1016)
  FROM your_table
 WHERE <<predicate that returns just the sample data you posted>>

What application are you using to view the data? What is the client's NLS_LANG set to?

What is the database and national character set? Is the data stored in a VARCHAR2 column? Or NVARCHAR2?

SELECT parameter, value
  FROM v$nls_parameters
 WHERE parameter LIKE '%CHARACTERSET';

If all the problem characters are stored in the database as 0x19 (decimal 25), your REPLACE would need to be something like

UPDATE table_name
   SET column1 = REPLACE(column1, chr(25), q'[']'),
       column2 = REPLACE(column2, chr(25), q'[']'),
       ...
       columnN = REPLACE(columnN, chr(25), q'[']')
 WHERE INSTR(column1,chr(25)) > 0
    OR INSTR(column2,chr(25)) > 0 
    ...
    OR INSTR(columnN,chr(25)) > 0