Select returns “¿” character

0
votes

My query:

SELECT op_arv
FROM my_table@OTHER_DB

returns:

d¿identification

The "¿" symbol should not be there.

If I then run:

SELECT 
 substr(op_arv,2,1) AS t_substr
,ascii(substr(op_arv,2,1)) AS t_ascii
,chr(ascii(substr(op_arv,2,1))) AS t_chr_ascii
,asciistr(substr(op_arv,2,1)) AS asciistr_1
FROM my_table@OTHER_DB

I get the following output:

¿
146
¿
\0092

The ASCII function returns 146, which corresponds to the ’ (apostrophe) symbol which makes sense. But if I try to "CHR()" the actual value, I will get ¿ instead of ’. CHR(146) gives me the correct symbol...

Running ASCIISTR function will return the Unicode value \0092 which is a unicode control character, not an apostrophe...

I am running Oracle 11gR2 with NLS_CHARACTERSET= WE8MSWIN1252. I am connecting (through a database link) to an Oracle database running with NLS_CHARACTERSET= WE8ISO8859P1.

Any ideas?

Thank you!

1
databaseoraclecharacter-set
The asciistr result is in hex; and 92 in base-16 is 146 in base-10, so that does match. What is your client characterset - e.g. from your NLS_LANG setting if you're using SQL*Plus (I'd guess US7ASCII); and your OS/shell/emulatar encoding? This looks like a client rendering issue, not a data or DB problem. – Alex Poole
Which tool do you use to run the select statement? – Wernfried Domscheit

1 Answers

0
votes

Most likely you did not set your NLS_LANG value and Oracle defaults it to AMERICAN_AMERICA.US7ASCII.

SQLplus inherits the character set from command line, you can interrogate/change the codepage with command chcp and then you must set NLS_LANG accordingly.

Example:

chcp 1252
set NLS_LANG=.WE8MSWIN1252
sqlplus ...

If you work on Linux then use locale charmap or echo $LANG to retrieve the encoding of your terminal.

It is not required to set NLS_LANG equal to your database character set, however the character set you choose must support your character ’