Why lowercase 'i' mapped to '?' after doing NLS_UPPER

Question

Can someone please explain why lowercase i in string abcdefghijklmnopqrstuvwxyz is mapped to ? whereas all other alphabets are correctly mapped to corresponding uppercase alphabets.

SQL> ALTER SESSION SET NLS_SORT="xturkish";

Session altered.

SQL> 
SQL> begin
  2   DBMS_OUTPUT.PUT_LINE('NLS_UPPER(abcdefghijklmnopqrstuvwxyz) = '
  3                || NLS_UPPER('abcdefghijklmnopqrstuvwxyz'));
  4  end;
  5  /
NLS_UPPER(abcdefghijklmnopqrstuvwxyz) = ABCDEFGH?JKLMNOPQRSTUVWXYZ

PL/SQL procedure successfully completed.

SQL> 

Update: If nls_sort is set to BINARY, the mapping happens as expected, lowercase i maps to a plane English alphabet I

Answer 1

The capital i in Turkish is İ (U+0130: Latin Capital Letter I with Dot Above), see https://codepoints.net/U+0130

The Turkish alphabet, which is a variant of the Latin alphabet, includes two distinct versions of the letter I, one dotted and the other dotless.

The dotless I, I ı, denotes the close back unrounded vowel sound (/ɯ/). Neither the upper nor the lower case version has a dot.

The dotted I, İ i, denotes the close front unrounded vowel sound (/i/). Both the upper and lower case versions have a dot.

Examples:

• İstanbul /isˈtanbuɫ/ (starts with an i sound, not an ı).

• Diyarbakır /dijaɾˈbakɯɾ/ (the first and last vowels are spelled and pronounced differently)

Looks like your Database character set does not support this character, what is your Database character set? Check with

SELECT * 
FROM V$NLS_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

I assume

DBMS_OUTPUT.PUT_LINE('NLS_UPPER(abcdefghijklmnopqrstuvwxyz) = '
   || NLS_UPPER(N'abcdefghijklmnopqrstuvwxyz')); 

should work.

You must set your NLS_LANG and console codepage properly for correct output. Here are some working examples:

C:\>set NLS_LANG=.AL32UTF8
C:\>chcp 65001
Active code page: 65001

C:\>sqlplus ...
select nls_upper('i', 'nls_sort = xturkish') from dual;



C:\>set NLS_LANG=.TR8MSWIN1254
C:\>chcp 1254
Active code page: 1254

C:\>sqlplus ...
select nls_upper('i', 'nls_sort = xturkish') from dual;




C:\>set NLS_LANG=.SE8ISO8859P3
C:\>chcp 28593
Active code page: 28593

C:\>sqlplus ...
select nls_upper('i', 'nls_sort = xturkish') from dual;




C:\>set NLS_LANG=.WE8ISO8859P9
C:\>chcp 28599
Active code page: 28599

C:\>sqlplus ...
select nls_upper('i', 'nls_sort = xturkish') from dual;

Answer 2

I can't explain it because I can't reproduce it. Something must be wrong on your machine. Here is my session, copied and pasted from SQL*Plus:

SQL> set serveroutput on
SQL> ALTER SESSION SET NLS_SORT="xturkish";

Session altered.

Elapsed: 00:00:00.01
SQL> begin
  2     DBMS_OUTPUT.PUT_LINE('NLS_UPPER(abcdefghijklmnopqrstuvwxyz) = '
  3                  || NLS_UPPER('abcdefghijklmnopqrstuvwxyz'));
  4    end;
  5  /
NLS_UPPER(abcdefghijklmnopqrstuvwxyz) = ABCDEFGHIJKLMNOPQRSTUVWXYZ

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>