Selecting Turkish Characters From Oracle Database

Speak less, do more :) Let's show it


SQL> create table turkish_char_test (s varchar2(100) );

Table created
SQL> insert into turkish_char_test(s) values('ş');

1 row inserted
SQL> insert into turkish_char_test(s) values('Ş');

1 row inserted
SQL> insert into turkish_char_test(s) values('i');

1 row inserted
SQL> insert into turkish_char_test(s) values('İ');

1 row inserted
SQL> commit;

Commit complete
SQL> select * from turkish_char_test where upper(s) like upper('%i%');

S
--------------------------------------------------------------------------------
i
SQL> select * from turkish_char_test where nls_upper(s,  'NLS_SORT = XTURKISH') like nls_upper('%i%', 'NLS_SORT = XTURKISH');

S
--------------------------------------------------------------------------------
i
İ
SQL> select * from turkish_char_test where nls_upper(s,  'NLS_SORT = XTURKISH') like nls_upper('%ş%', 'NLS_SORT = XTURKISH');

S
--------------------------------------------------------------------------------
ş
Ş
SQL> select * from turkish_char_test where nls_upper(s,  'NLS_SORT = XTURKISH') like nls_upper('%İ%', 'NLS_SORT = XTURKISH');

S
--------------------------------------------------------------------------------
i
İ
SQL> --- TRANSLATE
SQL> select * from turkish_char_test where upper(translate(s, 'İiŞş', 'IISS')) like '%'||upper(translate('İ', 'İiŞş', 'IISS'))||'%';

S
--------------------------------------------------------------------------------
i
İ

SQL>