関数 ② — 文字列関数（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）になることを確認してください。