Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

alter session set nls_length_semantics=char
CREATE TABLE t (
    long_field VARCHAR2(100)
);
SELECT
    column_name,
    data_length,
    char_length,
    char_col_decl_length,
    char_used
FROM
    all_tab_cols
WHERE
    table_name = 'T';


COLUMN_NAME
DATA_LENGTH
CHAR_LENGTH
CHAR_COL_DECL_LENGTH
CHAR_USED


LONG_FIELD
400
100
400
C


INSERT INTO t ( long_field ) VALUES ( 'abcdef' );
1 rows affected

CREATE TABLE t2
    AS
        SELECT
            substr(long_field, instr(long_field, 'c') + 1) short_field
        FROM
            t;
1 rows affected

SELECT
    column_name,
    data_length,
    char_length,
    char_col_decl_length,
    char_used
FROM
    all_tab_cols
WHERE
    table_name = 'T2';


COLUMN_NAME
DATA_LENGTH
CHAR_LENGTH
CHAR_COL_DECL_LENGTH
CHAR_USED


SHORT_FIELD
400
100
400
C