SQL to replace smart quotes

Question

Does anyone know an SQL command to replace MS Office smart quotes with their ASCII cousins? I am using an oracle database and the fields are of type varchar2

Answer 1

table update table set column = replace (replace (column, chr (147), '' '), chr (148),' '')

Answer 2

REPLACE(REPLACE(str, '`', ''''), '´', '''') 

Or am I missing your question?

Answer 3

I had a similar problem. For me, after the quotes were stored in the database, they appeared, thus, “”.

 SELECT abstract FROM foo WHERE version = '1.0' and newscode = 'au20309'; 

MaeÂr ffordd gynaliadwy y mae bwyd yn cael ei dyfu, ei brynu aÂi baratoi ...

This is how I replaced them. First find the ascii value for this unusual “” character.

 SELECT ascii('Â') FROM DUAL; -- returns 50050 

Then use the chr function to render "Â". || The function combines two characters. The q function is useful for quoting a smart quote string.

 SELECT REPLACE(abstract,chr(50050) || q'#'#' , q'#'#') FROM foo WHERE version = '1.0' and newscode = 'au20309'; 

Mae'r ffordd gynaliadwy y mae bwyd yn cael ei dyfu, ei brynu a'i baratoi ...

This worked perfectly for me on our Oracle 10 system.

Answer 4

TRANSLATE would be more suitable than REPLACE.

 TRANSLATE(str, '`´', '''''') 

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions204.htm#sthref2477

Answer 5

 update table set column = replace( column, string_to_replace, [ replacement_string ] )