6

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

GBa
  • 17,509
  • 15
  • 49
  • 67

5 Answers5

4

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

Rich
  • 671
  • 6
  • 10
  • 1
    This is right if the character set in the database is code page 1252 (Western European) or a few similar code pages. For other charsets, smart quotes may be encoded differently. – bobince Mar 19 '09 at 19:10
2
REPLACE(REPLACE(str, '`', ''''), '´', '''')

Or am I missing your question?

erikkallen
  • 33,800
  • 13
  • 85
  • 120
1

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.

aberpaul
  • 437
  • 3
  • 6
0

TRANSLATE would be more appropriate than REPLACE.

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

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

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
-2
update table set column = replace( column, string_to_replace, [ replacement_string ] )
cdonner
  • 37,019
  • 22
  • 105
  • 153