【SQL】TRIM関数完全ガイド｜空白・指定文字の削除・LEADING/TRAILING/BOTH・BTRIM・実務パターンまで解説

-- 両端の半角スペースを除去
SELECT TRIM('  Hello World  ') AS result;
-- 結果: 'Hello World'  ※中間のスペースは残る

-- カラムに適用
SELECT TRIM(name) AS trimmed_name FROM customers;

-- LTRIM: 先頭（左）のみ
SELECT LTRIM('  Hello  ') AS result;  -- 'Hello  '

-- RTRIM: 末尾（右）のみ
SELECT RTRIM('  Hello  ') AS result;  -- '  Hello'

-- LTRIM + RTRIM の組み合わせ（TRIM相当・古いSQL Server対応）
SELECT LTRIM(RTRIM('  Hello  ')) AS result;  -- 'Hello'

-- LEADING: 先頭から指定文字を除去
SELECT TRIM(LEADING 'x' FROM 'xxHello Worldxx');
-- 結果: 'Hello Worldxx'

-- TRAILING: 末尾から指定文字を除去
SELECT TRIM(TRAILING 'x' FROM 'xxHello Worldxx');
-- 結果: 'xxHello World'

-- BOTH: 両端から指定文字を除去（デフォルト）
SELECT TRIM(BOTH 'x' FROM 'xxHello Worldxx');
-- 結果: 'Hello World'

-- 文字を省略した場合: スペースが対象（以下は等価）
SELECT TRIM('  Hello  ');
SELECT TRIM(BOTH ' ' FROM '  Hello  ');
SELECT TRIM(BOTH FROM '  Hello  ');

-- 連続する先頭・末尾の x をすべて除去
SELECT TRIM(BOTH 'x' FROM 'xxxABCxxx');   -- 'ABC'
SELECT TRIM(BOTH 'x' FROM 'xxxABCxXxx'); -- 'ABCxX'  ← 大文字Xは別文字

-- 複数文字を指定しても「その文字列パターン」ではなく
-- 「その文字を1文字ずつ」除去（DBMSによって動作が異なる注意点）
-- MySQL / Oracle: 1文字ずつ評価
SELECT TRIM(BOTH 'ab' FROM 'ababHelloabab');
-- MySQL:  'Hello'（a または b を個別に除去）
-- Oracle: 'Hello'（同様）

-- スペース以外のいろんな文字を除去する例
SELECT TRIM(LEADING '0' FROM '000123');   -- '123'（ゼロパディング除去）
SELECT TRIM(TRAILING '/' FROM 'path/');   -- 'path'（末尾スラッシュ除去）
SELECT TRIM(BOTH '"' FROM '"Hello"');     -- 'Hello'（引用符除去）

-- MySQL: TRIM / LTRIM / RTRIM すべて対応
SELECT TRIM('  Hello  ');                           -- 'Hello'
SELECT TRIM(LEADING '0' FROM '00123');              -- '123'
SELECT TRIM(TRAILING '.' FROM 'price.');            -- 'price'
SELECT TRIM(BOTH '#' FROM '##tag##');               -- 'tag'

-- LTRIM / RTRIM: 半角スペースのみ（文字指定不可）
SELECT LTRIM('   Hello');                           -- 'Hello'
SELECT RTRIM('Hello   ');                           -- 'Hello'

-- PostgreSQL: TRIM / LTRIM / RTRIM + 独自の BTRIM
SELECT TRIM('  Hello  ');                           -- 'Hello'
SELECT TRIM(LEADING FROM '  Hello  ');              -- 'Hello  '
SELECT TRIM(TRAILING FROM '  Hello  ');             -- '  Hello'

-- PostgreSQL独自: LTRIM / RTRIM に削除文字集合を指定可能
SELECT LTRIM('xxHello', 'x');                       -- 'Hello'  ← 文字指定OK
SELECT RTRIM('Helloyy', 'y');                       -- 'Hello'

-- BTRIM: 両端から文字「集合」に含まれる文字を削除
SELECT BTRIM('xyzHellozyx', 'xyz');                 -- 'Hello'
-- 'x','y','z' のどれかに該当する文字を両端から除去

-- PostgreSQL TRIM BOTH は標準構文で使える
SELECT TRIM(BOTH 'x' FROM 'xxxHelloxxx');           -- 'Hello'

-- Oracle: TRIM / LTRIM / RTRIM 対応（LEADING/TRAILING/BOTH指定可）
SELECT TRIM('  Hello  ') FROM dual;                 -- 'Hello'
SELECT TRIM(LEADING ' ' FROM '  Hello  ') FROM dual; -- 'Hello  '
SELECT TRIM(TRAILING ' ' FROM '  Hello  ') FROM dual;-- '  Hello'

-- Oracle の LTRIM / RTRIM は文字集合を指定できる（PostgreSQLと同様）
SELECT LTRIM('xxHello', 'x') FROM dual;             -- 'Hello'
SELECT RTRIM('Hello##', '#') FROM dual;             -- 'Hello'

-- 複数文字の集合（順不同で両端から除去）
SELECT LTRIM('xyzHello', 'xyz') FROM dual;          -- 'Hello'

-- SQL Server 2017以降: TRIM 関数が追加された
SELECT TRIM('  Hello  ');                           -- 'Hello'（SQL Server 2017+）

-- SQL Server 2017+: TRIM に除去文字を指定可能（拡張構文）
SELECT TRIM('0' FROM '0001234');                    -- '1234'（SQL Server 2022+）

-- SQL Server 2016以前: LTRIM / RTRIM で代替
SELECT LTRIM(RTRIM('  Hello  '));                   -- 'Hello'

-- LTRIM / RTRIM: SQL Serverは文字指定不可（スペースのみ）

-- 'x', 'y', 'z' のいずれかを両端から除去
SELECT BTRIM('xyzHellozyx', 'xyz');   -- 'Hello'
SELECT BTRIM('zzzHello', 'xyz');      -- 'Hello'  z を除去
SELECT BTRIM('zyxHello', 'xyz');      -- 'Hello'  順序無関係

-- 数字を両端から除去
SELECT BTRIM('123Hello456', '0123456789');  -- 'Hello'

-- スペースと特定記号を同時に除去
SELECT BTRIM('  ##tag##  ', ' #');    -- 'tag'  スペースと#を両端から除去

-- LTRIM / RTRIM でも文字指定可能（PostgreSQL・Oracle）
SELECT LTRIM('###Hello', '#');        -- 'Hello'
SELECT RTRIM('Hello###', '#');        -- 'Hello'

-- タブ（CHR(9)）を削除
SELECT REPLACE(col, CHR(9), '') FROM table;

-- 改行LF（CHR(10)）を削除
SELECT REPLACE(col, CHR(10), '') FROM table;

-- 改行CR（CHR(13)）を削除（Windows改行対策）
SELECT REPLACE(col, CHR(13), '') FROM table;

-- スペース・タブ・改行をまとめて削除
SELECT REPLACE(REPLACE(REPLACE(TRIM(col), CHR(9), ''), CHR(10), ''), CHR(13), '')
FROM table;

-- MySQL では CHAR() 関数を使う（CHR()ではない）
SELECT REPLACE(col, CHAR(10), '') FROM table;   -- MySQL

-- SQL Server では CHAR() を使う
SELECT REPLACE(col, CHAR(13) + CHAR(10), '') FROM table;  -- SQL Server CRLF

-- PostgreSQL: 空白類（スペース・タブ・改行）を正規表現で両端から除去
SELECT REGEXP_REPLACE(col, '^[\s]+|[\s]+$', '', 'g') FROM table;

-- または: 制御文字を空文字に置換
SELECT REGEXP_REPLACE(col, '[\x00-\x1F]', '', 'g') FROM table;

-- NG: col にインデックスがあっても使われない
SELECT * FROM customers WHERE TRIM(name) = '田中 太郎';
SELECT * FROM products  WHERE LTRIM(RTRIM(code)) = 'P001';

-- STEP1: 対象行を確認
SELECT COUNT(*) FROM customers WHERE name != TRIM(name);

-- STEP2: 差分を目視確認
SELECT name, TRIM(name) AS cleaned FROM customers WHERE name != TRIM(name);

-- STEP3: UPDATE でデータを修正
UPDATE customers
SET    name = TRIM(name)
WHERE  name != TRIM(name);

-- STEP4: 以後はアプリ側でTRIMしてからINSERT/UPDATEする

-- Oracle: 関数ベースインデックス
CREATE INDEX idx_trim_name ON customers (TRIM(name));
-- → WHERE TRIM(name) = '田中' でインデックスが使われる

-- PostgreSQL: 式インデックス
CREATE INDEX idx_trim_name ON customers ((TRIM(name)));
-- → WHERE TRIM(name) = '田中' でインデックスが使われる

-- MySQL 8.0+: 関数インデックス
CREATE INDEX idx_trim_name ON customers ((TRIM(name)));
-- → WHERE TRIM(name) = '田中' でインデックスが使われる（8.0.13+）

-- SQL Server: 計算列 + インデックスで代替
ALTER TABLE customers ADD name_trimmed AS LTRIM(RTRIM(name));
CREATE INDEX idx_name_trimmed ON customers (name_trimmed);

-- データ件数が少なく、インデックス不要な場合
SELECT * FROM customers WHERE name LIKE '%田中 太郎%';

-- または: TRIM結果と直接比較せず余裕を持たせた比較
SELECT * FROM customers
WHERE  TRIM(name) LIKE '%田中%';  -- 完全一致でなくとも検索できる

-- 一時テーブルにインポート後、クリーニングして本番テーブルへ
INSERT INTO products (code, name, price)
SELECT
    TRIM(code),
    TRIM(name),
    CAST(TRIM(price) AS DECIMAL(10,2))
FROM   import_staging
WHERE  TRIM(code) <> '';   -- 空行を除外

-- ダブルクォートも除去する場合
SELECT
    TRIM(BOTH '"' FROM TRIM(code))  AS code,
    TRIM(BOTH '"' FROM TRIM(name))  AS name
FROM import_staging;

-- 郵便番号からハイフンとスペースを除去
SELECT REPLACE(REPLACE(TRIM(zip_code), '-', ''), ' ', '') AS normalized_zip
FROM customers;

-- 電話番号の括弧・ハイフン・スペースを除去して数字のみに
SELECT REPLACE(REPLACE(REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', ''), '(', ''), ')', '')
AS digits_only_phone
FROM customers;

-- 先頭のゼロを除去（コード正規化）
SELECT TRIM(LEADING '0' FROM '000123')  AS code;  -- '123'

-- ただし TRIM(LEADING '0' FROM '0') は '' になるので注意
SELECT TRIM(LEADING '0' FROM '0');  -- '' (空文字)
-- 必要なら NULLIF + COALESCE で保護
SELECT COALESCE(NULLIF(TRIM(LEADING '0' FROM code), ''), '0') AS code
FROM table;

-- 末尾のゼロを除去（小数の整形）
SELECT TRIM(TRAILING '0' FROM '1.5000')  AS price;   -- '1.5'
SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM '100.000')) AS price;  -- '100'

-- アプリからのINSERT時にSQL側でもTRIMをかける（二重保護）
INSERT INTO messages (user_id, body, created_at)
VALUES (
    1,
    TRIM(:input_body),   -- バインドパラメータの前後空白を除去
    NOW()
);

-- SELECT時にクリーニングしてビューで提供
CREATE VIEW v_clean_customers AS
SELECT
    id,
    TRIM(name)          AS name,
    TRIM(email)         AS email,
    TRIM(address)       AS address
FROM customers;

-- 同一人物が「田中」「 田中 」「田中　」として登録されている重複を検出
SELECT
    TRIM(name)        AS normalized_name,
    COUNT(*)          AS cnt,
    GROUP_CONCAT(id)  AS ids        -- MySQL
FROM customers
GROUP BY TRIM(name)
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

-- PostgreSQL: 全種類の空白類（スペース・タブ・改行・全角）を両端から除去
SELECT REGEXP_REPLACE(col, '^[\s\u3000]+|[\s\u3000]+$', '', 'g')
FROM table;

-- Oracle: REGEXP_REPLACE で先頭末尾の空白類を除去
SELECT REGEXP_REPLACE(col, '^[[:space:]]+|[[:space:]]+$', '')
FROM table;

-- MySQL 8.0+: REGEXP_REPLACE で先頭末尾のスペース類を除去
SELECT REGEXP_REPLACE(col, '^[[:space:]]+|[[:space:]]+$', '')
FROM table;