In an AL32UTF8 database, the display width of a column defined with substr() is 4 times the character width:
SQL> create table t (a varchar2(10));
Table created.
SQL> insert into t values ('Hello');
1 row created.
SQL> select substr(a,1,3) from t; <-- 3 passed to substr()
SUBSTR(A,1,3
------------ <-- 12 dashes
Hel
In a different database with character set, say, WE8MSWIN1252, the width of the column in the last query is 3.
In this AL32UTF8 database, I can use the SQL*Plus column command to limit the displayed width to 3, or use substrb() in place of substr() in the query. (The table content is guaranteed to be pure ASCII text.) But is there a way to somehow adjust the environment setting to limit the display width to 3?
My test shows that NLS_LANG environment variable doesn't have any effect. I normally set it to .US8PC437 or AMERICAN_AMERICA.US8PC437 as recommended at
Configuring Oracle Database Globalization Support
The two databases are version 11.2.0.4. Client is 12c running on Windows 10, location United States, language English.