SQL to replace smart quotes

Question

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

Answer 1

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

Or am I missing your question?

Answer 2

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

Answer 3

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

Answer 4

TRANSLATE would be more appropriate than REPLACE.

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

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

Answer 5

I have 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 that unusual "Â" character.

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

Then use the chr function to render the "Â". The || function concatenate the two characters. The q function is useful to 'quote' the 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 just fine for me on our Oracle 10 system.