Sqlplus seems to treat special characters differently when cutting off lines in different sqlplus clients. I would like to understand why this happens.
A character with ascii value 14844051 (I think this is a hyphen) selected in sqlplus started in Unix seems to counts as two characters, whereas in sqlplus started on my local machine (which I will call cmd) it is treated as one character.
For example, when I display the string '12-45' (I shall use a normal dash in this example but the character in the middle represents the character with ascii value 14844051) with different linesizes, I get the following results in unix/cmd:
As a general note: in cmd the character (retrieved from the database) is displayed as 'â' but in unix it is displayed as a hyphen. However, I am not concerned about which character is printed, only how many positions it takes up and where the line breaks.
linesize 6 :
* in cmd: '12â45'
* in unix: '12-45'
linesize 5 - not OK:
* in cmd, the full string is on one line '12â45'
* in unix:
the characters '12-4' are displayed on one line and '5' is on the next
linesize 2: - not OK:
* in cmd, the result is OK:
line 1: '12'
line 2: 'â4'
line 3: '5'
* in unix, the result is not OK:
line 1: '12'
line 2: ''-'
line 3: '45'
In both instances the following NLS_PARAMETERS are set:
* NLS_NCHAR_CHARACTERSET: AL16UTF16
* NLS_CHARACTERSET: UTF8
This is on an Oracle database 10.2.0.4.0, sqlplus version unix: 188.8.131.52.0/ local machine: 10.2.0.1.0
Does anybody know what causes this and how to control it? I can replace the special character but I am more interested in why this happens.
For posteriority I will answer my own question:
explicitly converting the string from win1252 to UTF8 will prevent the issue:
select convert('some text here ' || chr(14844051) || ' and some more normal text','WE8MSWIN1252', 'UTF8') from dual;