【Oracle】文字列関数完全ガイド｜LPAD・RPAD・INSTR・TRANSLATE・CHR・ASCII・REPLACE・INITCAP の使い方と実務パターンを実例で解説

-- LPAD(str, n, pad): str の左を pad で埋めて全体を n 文字にする
SELECT
    LPAD('42', 6, '0')          AS zero_pad,      -- '000042'
    LPAD('ABC', 10)             AS space_pad,      -- '       ABC'（pad 省略=半角スペース）
    LPAD('TOOLONG', 4, '0')     AS truncated,      -- 'TOOL'（n より長い場合は左から切り捨て）
    RPAD('ABC', 8, '-')         AS right_pad,      -- 'ABC-----'
    RPAD('ABC', 8)              AS right_space      -- 'ABC     '
FROM DUAL;

-- ゼロ埋めの実務例：コード値を固定6桁に整形
SELECT
    product_code,
    LPAD(product_code, 6, '0') AS formatted_code
FROM products;

-- 進捗バーの生成（RPAD の応用）
SELECT
    task_name,
    pct_done,
    '[' || RPAD(RPAD('', ROUND(pct_done / 10), '#'), 10, '-') || '] '
        || pct_done || '%' AS progress_bar
FROM tasks;
-- 例: [######----] 60%

-- INSTR(str, substr [, start [, n]])
SELECT
    INSTR('HELLO WORLD', 'O')       AS first_o,      -- 5
    INSTR('HELLO WORLD', 'O', 6)    AS o_from6,      -- 8（6文字目以降で最初のO）
    INSTR('HELLO WORLD', 'O', 1, 2) AS second_o,     -- 8（1文字目から2番目のO）
    INSTR('HELLO WORLD', 'Z')       AS not_found,    -- 0（見つからない場合は0）
    INSTR('HELLO WORLD', 'O', -1)   AS backward      -- 8（start が負 = 後ろから検索）
FROM DUAL;

-- 実務例①: CSV の特定フィールドを切り出す
-- 'TANAKA,TARO,30' から2番目のカンマ以降を取得
SELECT
    SUBSTR('TANAKA,TARO,30',
           INSTR('TANAKA,TARO,30', ',', 1, 2) + 1) AS age_field  -- '30'
FROM DUAL;

-- 実務例②: ファイルパスからファイル名を取得
SELECT
    file_path,
    SUBSTR(file_path, INSTR(file_path, '/', -1) + 1) AS file_name
FROM file_log;
-- 例: '/home/user/data/report.csv' → 'report.csv'

-- 実務例③: 検索ヒットした行のみ取得（INSTR > 0 で WHERE 絞り込み）
SELECT product_name
FROM products
WHERE INSTR(UPPER(product_name), 'PRO') > 0;
-- LIKE '%PRO%' より柔軟な検索（start/n で制御可能）

SELECT
    REPLACE('Hello World', 'World', 'Oracle')   AS basic,        -- 'Hello Oracle'
    REPLACE('A-B-C-D', '-', '/')               AS delimiter,    -- 'A/B/C/D'
    REPLACE('AAABBBCCC', 'B', '')              AS delete_b,     -- 'AAACCC'（空文字で置換=削除）
    REPLACE('AAABBBCCC', 'B')                  AS delete_b2     -- 'AAACCC'（NULL で置換も削除）
FROM DUAL;

-- 実務例①: 全角スペースを半角スペースに統一
SELECT
    REPLACE(REPLACE(customer_name,
        CHR(12288), ' '),    -- 全角スペース(U+3000) → 半角
        '  ', ' ')           -- 連続スペースを1つに
    AS clean_name
FROM customers;

-- 実務例②: カンマ区切りを別の区切りに変換
SELECT REPLACE(tag_list, ',', ' | ') AS formatted_tags FROM articles;

-- 実務例③: 電話番号のハイフン・スペースを除去
SELECT REPLACE(REPLACE(phone_number, '-', ''), ' ', '') AS clean_phone
FROM contacts;

-- TRANSLATE(str, from_chars, to_chars)
-- from_chars の各文字 → to_chars の対応文字に1対1で置換
SELECT
    TRANSLATE('Hello World', 'aeiou', 'AEIOU')  AS vowel_upper,  -- 'HEllO WOrld'
    TRANSLATE('ABC123DEF', 'ABCDEF', 'abcdef')  AS lower_alpha,  -- 'abc123def'
    -- from_chars に余分な文字があると削除される
    TRANSLATE('A1B2C3', 'ABC', 'abc')           AS delete_nums   -- 'a b c'ではなく'abc'に1,2,3が削除される
FROM DUAL;

-- 実務例①: 数字以外の文字をすべて除去（電話番号クレンジング）
-- TRANSLATE のトリック: to_chars を1文字短くして先頭1文字だけ生き残らせる
SELECT
    phone_number,
    -- '0' を基準文字にして保持し、英字・記号(ABC...-() )を削除対象に
    TRANSLATE(phone_number,
              '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ -+().',
              '0123456789')   AS digits_only
FROM contacts;

-- 実務例②: 全角数字を半角数字に変換
SELECT
    TRANSLATE(price_str,
              '０１２３４５６７８９',   -- 全角数字
              '0123456789')              -- 半角数字
    AS half_price
FROM items;

-- CHR(n): 文字コード → 文字
SELECT
    CHR(65)    AS char_a,     -- 'A'（ASCII 65）
    CHR(97)    AS char_a2,    -- 'a'（ASCII 97）
    CHR(10)    AS lf,         -- 改行（LF、Line Feed）
    CHR(13)    AS cr,         -- キャリッジリターン（CR）
    CHR(9)     AS tab,        -- タブ
    CHR(12288) AS zenkaku_sp  -- 全角スペース（U+3000）※NLS_CHARACTERSET が UTF8/AL32UTF8 の場合
FROM DUAL;

-- ASCII(str): 文字 → 文字コード（先頭1文字のみ）
SELECT
    ASCII('A')      AS code_a,    -- 65
    ASCII('ABC')    AS code_abc,  -- 65（先頭のAのコードのみ）
    ASCII(' ')      AS code_sp    -- 32
FROM DUAL;

-- 実務例①: 改行コードを除去する（REPLACE + CHR）
SELECT
    REPLACE(REPLACE(memo, CHR(13) || CHR(10), ' '),  -- CRLF → スペース
            CHR(10), ' ')                              -- LF → スペース
    AS single_line_memo
FROM notes;

-- 実務例②: タブ区切りデータを整形する
SELECT REPLACE(raw_data, CHR(9), ',') AS csv_format FROM import_log;

-- 実務例③: 制御文字（印字不可文字）を一括除去
-- ASCII コードが 32 未満の制御文字をスペースに置換
SELECT REGEXP_REPLACE(data_col, '[' || CHR(0) || '-' || CHR(31) || ']', '') AS clean_data
FROM raw_data;

SELECT
    UPPER('hello world')     AS upper_case,   -- 'HELLO WORLD'
    LOWER('HELLO WORLD')     AS lower_case,   -- 'hello world'
    INITCAP('hello world')   AS title_case,   -- 'Hello World'
    INITCAP('oracle sql 23ai guide') AS title2  -- 'Oracle Sql 23Ai Guide'
FROM DUAL;

-- 実務例①: 大文字小文字を無視した検索
SELECT employee_id, last_name
FROM employees
WHERE UPPER(last_name) = UPPER(:search_name);
-- インデックスを有効にするには関数ベースインデックスを作成:
-- CREATE INDEX idx_emp_upper ON employees (UPPER(last_name));

-- 実務例②: 氏名の正規化（入力値を統一フォーマットに）
SELECT
    INITCAP(TRIM(LOWER(full_name))) AS normalized_name
FROM customers;
-- 'TANAKA taro  ' → 'Tanaka Taro'

-- || 演算子（推奨）: 複数の値を連結。NULL を連結すると無視される
SELECT
    'Hello' || ' ' || 'World'     AS concat_op,    -- 'Hello World'
    'Hello' || NULL || 'World'    AS with_null,     -- 'HelloWorld'（NULLは無視）
    NULL || NULL                  AS null_null       -- NULL
FROM DUAL;

-- CONCAT 関数: 引数は2つのみ。3つ以上は入れ子にするか || を使う
SELECT
    CONCAT('Hello', ' World')                      AS two_args,   -- 'Hello World'
    CONCAT(CONCAT('A', 'B'), 'C')                  AS three_args  -- 'ABC'（入れ子）
FROM DUAL;

-- 実務例: 氏名・住所の連結（NULL セーフな結合）
SELECT
    last_name || ' ' || first_name       AS full_name,
    -- prefecture が NULL の場合に余分なスペースが入らないようにする
    NVL2(prefecture, prefecture || ' ', '') || city AS address
FROM customers;