Single Quote Apostrophe Marks Change to Upside Down Question Marks
Question: We are extracting columns from Oracle
tables that contain single quote names with apostrophes:
O'Reilly
O'Malley
The flat files are changing the quote marks (apostrophes) into
upside-down question marks, displaying a special character ' as ¿
How do we avoid this?
Answer:
If you are on Windows, this is a "single quote" conversion to a
upside-down question mark is a common problem may might be fixed by
converting to a character set such as WE8MSWIN1252.
Try this test in SQL*Plus:
set nls_lang =
WE8MSWIN1252
export
nls_lang
spool
myfile.txt
select
last_name from mytab
spool off;
The very first step in diagnosing a special character in Oracle
SQL output is to consult the
Oracle National Language Support documentation for what
character sets supports the single quote apostrophe on your OS flat
files, usually a UNICODE type character set.
Next, read these important notes on
testing "special
characters" as part of your nls_lang setting.
As you see, Oracle will replace characters that are not within
its character set with the upside-down question mark and your
problem is that either the database or the flat files has an
incorrect character set as defined by your nls_lang
parameter. The upside-down question mark means that the character
set does not support the quote mark. See
MOSC Note 275138.1 (Cannot
Map Unicode To Oracle Character) for complete resolution details.
An apostrophe is not really a "special" character, and
internally, a single quote (apostrophe) is represented by an ASCII
"0x27". You need to choose a character set that includes single
quotes.
An Interweb search says that an apostrophe can be represented in
the Windows WE8MSWIN1252 by at least four different codes:
- 0x91 - left single quotation
mark (')
- 0x92 - right single quotation mark (')
- 0x27 -
standard ASCII apostrophe (')
Beware, the Windows single quote character (aka smart quote) is
non-standard and it is is not contained in the WE8ISO8859P1
character set.
