【Oracle】LENGTH / LENGTHB で文字数・バイト数を取得する方法｜全LENGTH関数・VARCHAR2 BYTE/CHAR・VSIZE・NLS設定まで解説

-- LENGTH: 文字数を返す
SELECT LENGTH('Oracle') AS char_count FROM dual;   -- 6
SELECT LENGTH('日本語')  AS char_count FROM dual;   -- 3

-- LENGTHB: バイト数を返す
SELECT LENGTHB('Oracle') AS byte_count FROM dual;  -- 6（半角英数は 1 バイト/文字）
SELECT LENGTHB('日本語') AS byte_count FROM dual;  -- 9（AL32UTF8 の場合: 3 バイト/文字）
-- JA16SJIS の場合は 6（2 バイト/文字）

-- データベースの文字セットを確認
SELECT parameter, value FROM NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_CHARACTERSET';
-- 結果例: AL32UTF8

-- 通常の日本語: すべて同じ結果
SELECT
    LENGTH('日本語')  AS len,     -- 3
    LENGTHB('日本語') AS lenb,    -- 9（AL32UTF8）
    LENGTHC('日本語') AS lenc,    -- 3
    LENGTH2('日本語') AS len2,    -- 3
    LENGTH4('日本語') AS len4     -- 3
FROM dual;

-- 絵文字（サロゲートペア文字）: LENGTH2 だけ異なる場合がある
-- 例: U+1F600（?）は UTF-16 で 2 コードユニット

-- テーブルの列が BYTE か CHAR かを確認
SELECT column_name, data_type, data_length, char_used,
       CASE char_used
           WHEN 'B' THEN 'BYTE セマンティクス'
           WHEN 'C' THEN 'CHAR セマンティクス'
       END AS semantics
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

-- CHAR_USED 列: B=BYTE, C=CHAR

-- セッションのデフォルト LENGTH_SEMANTICS を確認
SELECT parameter, value FROM NLS_SESSION_PARAMETERS
WHERE parameter = 'NLS_LENGTH_SEMANTICS';
-- 結果: BYTE（デフォルト）または CHAR

-- セッション単位で変更
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;
-- 以降の CREATE TABLE で VARCHAR2(100) は VARCHAR2(100 CHAR) として作成される

-- 文字列: LENGTHB と同じ結果
SELECT VSIZE('ABC')   AS vsize_abc FROM dual;    -- 3
SELECT VSIZE('日本語') AS vsize_jp FROM dual;     -- 9（AL32UTF8）

-- NUMBER 型: 内部格納サイズ
SELECT VSIZE(0)       AS vsize_0 FROM dual;       -- 1
SELECT VSIZE(123)     AS vsize_123 FROM dual;     -- 3
SELECT VSIZE(123.456) AS vsize_dec FROM dual;     -- 5

-- DATE 型: 固定 7 バイト
SELECT VSIZE(SYSDATE) AS vsize_date FROM dual;    -- 7

-- NULL
SELECT VSIZE(NULL)    AS vsize_null FROM dual;    -- NULL

-- Oracle では空文字列は NULL として扱われる
SELECT LENGTH('')    AS len_empty FROM dual;  -- NULL（0 ではない!）
SELECT LENGTH(NULL)  AS len_null FROM dual;   -- NULL
SELECT LENGTHB('')   AS lenb_empty FROM dual;  -- NULL
SELECT LENGTHB(NULL) AS lenb_null FROM dual;   -- NULL

-- 半角スペース 1 文字は空文字列ではない
SELECT LENGTH(' ')   AS len_space FROM dual;   -- 1

-- 空文字列 / NULL を判定する安全なパターン
SELECT employee_id, name,
       NVL(LENGTH(name), 0) AS name_length,
       CASE WHEN name IS NULL THEN '空'
            ELSE TO_CHAR(LENGTH(name)) || '文字'
       END AS status
FROM employees;

-- CLOB に対して LENGTH は使える（文字数を返す）
SELECT LENGTH(clob_column) AS char_count FROM documents;

-- DBMS_LOB.GETLENGTH: CLOB の文字数（NUMBER 型で返す）
SELECT DBMS_LOB.GETLENGTH(clob_column) AS char_count FROM documents;

-- LENGTHB は CLOB に直接使えない（暗黙変換で 32767 バイトまで）
-- CLOB のバイト数を取得するには DBMS_LOB.GETLENGTH + LENGTHB を組み合わせるか、
-- CLOB を BLOB に変換して DBMS_LOB.GETLENGTH で取得

-- CLOB のバイト数（概算: 文字数 × 最大バイト/文字）
SELECT LENGTH(clob_column) * 3 AS max_bytes FROM documents;  -- AL32UTF8 の場合

-- name 列が VARCHAR2(100 BYTE) の場合、バイト数超過する行を事前検出
SELECT id, name,
       LENGTH(name)  AS char_count,
       LENGTHB(name) AS byte_count
FROM staging_data
WHERE LENGTHB(name) > 100;
-- この行を INSERT すると ORA-12899 が発生する

-- 文字数とバイト数が異なる = マルチバイト文字を含む
SELECT id, name,
       LENGTH(name)  AS chars,
       LENGTHB(name) AS bytes,
       LENGTHB(name) - LENGTH(name) AS multi_byte_extra
FROM products
WHERE LENGTH(name) <> LENGTHB(name);

-- 全角文字の割合を計算
SELECT id, name,
       ROUND((LENGTHB(name) - LENGTH(name)) * 100.0
             / NULLIF(LENGTHB(name), 0), 1) AS multibyte_pct
FROM products
WHERE LENGTH(name) <> LENGTHB(name);

-- 各 VARCHAR2 列で最大バイト数のデータを確認
SELECT 'NAME' AS col, MAX(LENGTHB(name)) AS max_bytes FROM employees
UNION ALL
SELECT 'ADDRESS', MAX(LENGTHB(address)) FROM employees
UNION ALL
SELECT 'EMAIL', MAX(LENGTHB(email)) FROM employees;

-- 列定義の 80% 以上を使用しているデータを検出
SELECT employee_id, name, LENGTHB(name) AS bytes,
       100 AS col_limit,
       ROUND(LENGTHB(name) / 100 * 100, 1) AS usage_pct
FROM employees
WHERE LENGTHB(name) > 100 * 0.8;  -- name が VARCHAR2(100 BYTE) の場合

-- 名前の文字数の分布を確認
SELECT LENGTH(name) AS char_count, COUNT(*) AS cnt
FROM employees
WHERE name IS NOT NULL
GROUP BY LENGTH(name)
ORDER BY char_count;