関数 ② — 文字列関数（LENGTH / TRIM / REPLACE / UPPER / SUBSTR）

本記事で使うデータ — staff テーブル

関数記事の 2 本目は文字列関数です。文字列の長さ・空白除去・置換・大文字小文字変換・部分文字列の取り出しといった、運用中のテーブルから取り出したテキストを加工する場面で頻出する関数を順に押さえます。

題材は前回と同じstaffテーブル（10 行: name / city / salary など）です。name列の人名を主役に、長さの計算、姓の置換、大文字化、頭文字の取り出しを 4 つの演習で実行します。

演習に入る前に、staffテーブルの列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(staff);で列名・型・主キーを確認してください。

② SELECT * FROM staff LIMIT 5;で先頭 5 行のデータをプレビューしてください。

文字列の長さ — `LENGTH`と`CHAR_LENGTH`

文字列の長さを数える関数は、本講座のコンソール (SQLite) と MySQL で挙動が違うので注意が必要です。

- 本講座のコンソール (SQLite): LENGTH(s)は文字数を返す（'あいう'は 3）

- MySQL: LENGTH(s)はバイト数を返し（'あいう'は 9）、文字数を取りたいときはCHAR_LENGTH(s)を使う

本記事の演習ではLENGTH()を使いますが、この違いは把握しておくのが重要です。

-- 1) 文字数を数える（本講座のコンソールでは LENGTH が文字数を返す）
SELECT LENGTH('Alice');         -- 5
SELECT LENGTH('あいう');         -- 3 (本講座), 9 (MySQL の LENGTH はバイト数)

-- 2) 列の長さで並び替え
SELECT name, LENGTH(name) AS name_len FROM staff
ORDER BY name_len DESC;

-- 参考: MySQL では文字数は CHAR_LENGTH(s)
--   SELECT CHAR_LENGTH('あいう') FROM dual;  -- 3

従業員一覧で「フルネームが長い社員 TOP 3」を表示したい、という要件を想定します。（正しく実行できれば解説が表示されます）

① staffテーブルからname、LENGTH(name)をname_lenという別名で、計 2 列を取り出してください。

② name_lenの降順で並べてください。同じ長さの行はnameの昇順（アルファベット順）で並べる多段ソートにしてください。

③ 先頭 3 行に絞ってください。

④ 結果が 3 行（Iris Watanabe 13 / Alice Tanaka 12 / Carol Tanaka 12）になることを確認してください。

空白除去と置換 — `TRIM`と`REPLACE`

実務のテーブルでは「コピペで前後にスペースが混入した」「移行前のシステムで全角スペースが入っている」など、意図しない空白が入っている場合があります。TRIM(s)は文字列の前後の空白を削除し、LTRIM(s)は左側だけ、RTRIM(s)は右側だけを削除します。

REPLACE(s, find, replace)は文字列sの中のfindをすべて replaceに置換します。「Tanaka姓を田中に表記揺れ統一」「メールアドレスのドメイン部を別ドメインに置換」のように、表記の正規化や移行で頻出する関数です。UPDATEと組み合わせれば、テーブル本体の値を書き換えることもできます。

-- 1) 空白除去: TRIM / LTRIM / RTRIM
SELECT TRIM('  Hello  ');     -- 'Hello'
SELECT LTRIM('  Hello  ');    -- 'Hello  '
SELECT RTRIM('  Hello  ');    -- '  Hello'

-- 列に対する TRIM と長さの差で「空白入り」の行を検出
SELECT name FROM staff
WHERE LENGTH(name) <> LENGTH(TRIM(name));

-- 2) 置換: REPLACE
SELECT REPLACE('I LIKE APPLE', 'APPLE', 'BANANA');
-- 'I LIKE BANANA'

-- 列に対する REPLACE
SELECT name, REPLACE(name, 'Tanaka', '田中') AS jp_name
FROM staff;

TRIM の応用 — UPDATE と組み合わせて表記ゆれを直す

TRIMは読み取りだけでなく、UPDATEと組み合わせてテーブル本体の値を直接書き換えるのにも使えます。たとえば前後に半角スペースが混入した name 列を一括で正規化したいときはUPDATE staff SET name = TRIM(name) WHERE LENGTH(name) <> LENGTH(TRIM(name));のように、先に WHERE で対象を絞ってから UPDATE するのが安全です。WHEREを抜くと全行に TRIM がかかり（実害は少ないですが）、無駄な UPDATE が走ります。REPLACEも同じ要領でUPDATE 表 SET 列 = REPLACE(列, 'A', 'B')のように使え、表記ゆれの一括修正の基本テクニックとして使えます。

「英語表記の社員リストを和名表記に直したい」という要件を想定します。

① staffテーブルからname、REPLACE(name, 'Tanaka', '田中')をjp_nameという別名で、計 2 列を取り出してください。

② 結果が 10 行になり、Tanakaを含む 4 名（Alice / Carol / Frank / Jack）のjp_nameが'Alice 田中' / 'Carol 田中' ... のように置換されていることを確認してください。

大文字小文字と部分文字列 — `UPPER` / `LOWER` / `SUBSTR`

UPPER(s)は文字列をすべて大文字、LOWER(s)はすべて小文字に変換します。検索条件で大文字小文字を統一したいとき（WHERE UPPER(email) = 'X@Y.COM'）や、表示形式を揃えたいときに使います。日本語など大文字小文字の概念がない文字には影響しません。

SUBSTR(s, start, length)は文字列のstart文字目からlength文字を取り出します（SUBSTRINGでも書けます）。SQL の文字位置は 1 始まりで、SUBSTR('Alice Tanaka', 1, 5)は'Alice'を返します。3 引数目を省略すると最後までを取り出し、SUBSTR(s, -3)のようにstartを負にすると末尾から数える動きになります。

-- 1) UPPER / LOWER
SELECT UPPER('apple'), LOWER('APPLE');
-- 'APPLE', 'apple'

-- 列に対して: 名前を大文字 / 小文字で並べる
SELECT name, UPPER(name) AS upper_name, LOWER(name) AS lower_name
FROM staff;

-- 2) SUBSTR: 文字位置は 1 始まり
SELECT SUBSTR('Alice Tanaka', 1, 5);   -- 'Alice'
SELECT SUBSTR('Alice Tanaka', 7, 6);   -- 'Tanaka'
SELECT SUBSTR('Alice Tanaka', 7);      -- 'Tanaka'  -- length 省略で末尾まで
SELECT SUBSTR('Alice Tanaka', -6);     -- 'Tanaka'  -- 負数で末尾から

-- 列の頭文字 1 文字
SELECT name, SUBSTR(name, 1, 1) AS initial FROM staff;

-- 参考: REVERSE は MySQL / Oracle の関数で、本講座のコンソールには無い
--   SELECT REVERSE('Alice Tanaka');  -- 'akanaT ecilA' (MySQL では動く)

「社員のファイル名を頭文字でグルーピングするため、頭文字を取り出してソートしたい」という要件を想定します。

① staffテーブルからname、UPPER(name)をupper_name、SUBSTR(name, 1, 1)をinitialの 3 列を取り出してください。

② initialの昇順（アルファベット順）、同じ頭文字の行はnameの昇順で並べてください。

③ 結果が 10 行になり、先頭が Alice Tanaka / ALICE TANAKA / A、最後が Jack Tanaka / JACK TANAKA / J になることを確認してください。

文字列の位置を探す — `INSTR`

INSTR(対象, 検索文字列)は、対象文字列の中で検索文字列が最初に現れる位置（何文字目か）を返す関数です。位置は1 始まりで、見つからなければ0を返します。たとえばINSTR('Alice Tanaka', ' ')は半角スペースが 6 文字目にあるので6を返します。

SUBSTRと組み合わせると、固定位置ではなく区切り文字を基準に文字列を切り出すことができます。「メールアドレスの@の前後を分割する」「フルネームから姓だけ取り出す」のような、各行で区切り位置が違う文字列の加工に頻出する組み合わせです。

-- 1) INSTR 単独 — スペースの位置を確認
SELECT name, INSTR(name, ' ') AS space_pos FROM staff;
-- 'Alice Tanaka' → 6, 'Bob Suzuki' → 4, ...

-- 2) SUBSTR と組み合わせて姓を取り出す
SELECT name,
       SUBSTR(name, INSTR(name, ' ') + 1) AS last_name
FROM staff;

-- 3) ' @ ' の前後を分割（メールアドレスの想定例）
SELECT INSTR('alice@example.com', '@'); -- 6

氏名の姓部分だけを取り出して、姓のアルファベット順に上位 5 名を表示する、という要件を想定します。staffのnameは「Alice Tanaka」のように半角スペース区切りで「名 姓」の形ですが、名前の長さは人によって違うので、固定位置で切るのではなくINSTRでスペースの位置を探してからその後ろを切り出します。

① staffテーブルからname、SUBSTR(name, INSTR(name, ' ') + 1)をlast_nameという別名で、計 2 列を取り出してください。

② last_nameの昇順、同姓はnameの昇順で並べる多段ソートにし、先頭 5 行に絞ってください。

③ 結果が 5 行（David Sato / Emi Sato / Henry Sato / Bob Suzuki / Grace Suzuki）になることを確認してください。