MySQL文字列関数まとめ：CONCAT・REPLACE・TRIMほか

black laptop computer turned-on displaying source code on table SQL

SQLで文字列を扱う場面は多く、氏名の結合・メールアドレスの切り出し・入力値の正規化など、実務で日常的に登場します。MySQLにはCONCAT・SUBSTRING・REPLACE・TRIMなど文字列専用の関数が豊富にありますが、LENGTHとCHAR_LENGTHの違いやCONCATにNULLが混入したときの挙動を知らないと、思わぬバグを生みます。この記事ではよく使う文字列関数の構文・実例・注意点を早見表つきで整理します。

このシナリオで考える

ECサイトのユーザー管理テーブルを想定したusersで解説を進めます。

CREATE TABLE users (
  id    INT          PRIMARY KEY AUTO_INCREMENT,
  sei   VARCHAR(50),
  mei   VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);

INSERT INTO users (sei, mei, email, phone) VALUES
('山田', '太郎', 'taro.yamada@example.com',   '090-1234-5678'),
('鈴木', '花子', 'hanako.suzuki@example.com', ' 080-9876-5432 '),
('田中', NULL,  'tanaka@example.com',         '070-1111-2222');

id=2のphoneには前後にスペースが混入しており、id=3のmeiはNULLです。現場ではこうした「汚れたデータ」を扱う機会が多く、文字列関数がその整形を担います。

CONCATとCONCAT_WSで文字列を結合する

CONCAT(str1, str2, ...)は複数の文字列を連結して1つの文字列を返します。姓と名を結合してフルネームを生成するのが典型的な使い方です。

SELECT CONCAT(sei, ' ', mei) AS full_name
FROM users;
full_name
山田 太郎
鈴木 花子
NULL

id=3の結果がNULLになっています。引数に1つでもNULLが含まれると結果全体がNULLになるのがCONCATの重要な性質です。

NULL列を安全に結合するにはCONCAT_WS()（With Separator）を使います。第1引数にセパレータを指定し、NULLの引数は自動的にスキップされます。

SELECT CONCAT_WS(' ', sei, mei) AS full_name
FROM users;
full_name
山田 太郎
鈴木 花子
田中

セパレータ自体がNULLのときのみ結果がNULLになります。片方が欠けている可能性があるデータにはCONCAT_WSを使うのが安全です。

なお、PostgreSQLでは||演算子で文字列を連結できます。MySQLのデフォルトでは||は論理OR扱いのため、移植性を考えるとCONCATを使う方が無難です。

SUBSTRINGとREPLACEで文字列を加工する

SUBSTRING — 部分文字列を切り出す

構文: SUBSTRING(str, pos [, len])。位置は1始まりで、lenを省略すると末尾まで取り出します。SUBSTRは同義語として使えます。

-- @の位置を求め、その直後からドメインを取得する
SELECT email,
       SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;
email domain
taro.yamada@example.com example.com
hanako.suzuki@example.com example.com
tanaka@example.com example.com

LOCATE(substr, str)は部分文字列の先頭位置を返します（見つからない場合は0）。INSTR(str, substr)も同じ機能で、引数の順序だけ異なります。負の位置も指定でき、SUBSTRING(str, -3)は末尾3文字を切り出します。

REPLACE — 文字列をすべて置換する

構文: REPLACE(str, from_str, to_str)。str内でfrom_strと一致する箇所がすべてto_strに置き換えられます。大文字・小文字を区別します。

-- 電話番号からハイフンを除去して数字列にする
SELECT phone,
       REPLACE(TRIM(phone), '-', '') AS normalized_phone
FROM users;
phone normalized_phone
090-1234-5678 09012345678
‘ 080-9876-5432 ‘ 08098765432
070-1111-2222 07011112222

先にTRIMでスペースを除去してからREPLACEでハイフンを削っています。関数を入れ子にすることで複数の整形をまとめて行えます。正規表現で置換したい場合はMySQL 8.0以降のREGEXP_REPLACEが使えます。

TRIM・UPPER・LOWERで文字列を整える

TRIM — 前後の文字を除去する

構文: TRIM([{LEADING | TRAILING | BOTH} [remstr] FROM] str)

引数なしで使うと前後のスペースを削除します。フォーム入力やCSVインポートで紛れ込む余分なスペースの除去に欠かせません。

SELECT phone, TRIM(phone) AS trimmed
FROM users WHERE id = 2;
phone trimmed
‘ 080-9876-5432 ‘ 080-9876-5432

スペース以外を指定する場合は TRIM('0' FROM '00123400') と書きます（結果: '1234'）。前後の片方だけ対象にするならLTRIM(str)（左側）・RTRIM(str)（右側）が使えます。

UPPER・LOWER — 大文字・小文字を変換する

SELECT UPPER('hello mysql'), LOWER('HELLO MYSQL');
-- 結果: HELLO MYSQL, hello mysql

メールアドレスやユーザー名の正規化に使います。大文字・小文字混在のデータをINSERT前に統一するのが定石です。ただしBINARY・VARBINARY・BLOB型には効きません。バイト列には大文字・小文字の概念がないためです。

LENGTHとCHAR_LENGTHの違い

名前が紛らわしい2つの関数ですが、返す値の単位が異なります。

関数 返す値 日本語1文字（UTF-8）
LENGTH(str) バイト数 3
CHAR_LENGTH(str) 文字数（コードポイント数） 1 
SELECT LENGTH('日本語'),      -- 9
       CHAR_LENGTH('日本語');  -- 3

英数字のみなら両者は一致します。日本語・絵文字を含む場合はCHAR_LENGTHを使うのが基本です。「入力は20文字まで」という制限をLENGTH <= 20で実装すると、日本語7文字（= 21バイト）が弾かれるバグが生じます。入力文字数の検証には必ずCHAR_LENGTHを使ってください。

PostgreSQLでは逆にLENGTH(str)が文字数を返し、バイト数はOCTET_LENGTH(str)で取得します。DBを移植する際は注意が必要です。

よく使う文字列関数 早見表

関数 機能 MySQL PostgreSQL
CONCAT(s1, s2, ...) 文字列結合（NULLで全体NULL） ○ ○（||も可）
CONCAT_WS(sep, ...) セパレータ付き結合（NULLスキップ） ○ ○
SUBSTRING(s, pos[, n]) 部分文字列の取得（1始まり） ○ SUBSTRも可
REPLACE(s, from, to) 文字列を全置換（大文字小文字区別） ○ ○
REGEXP_REPLACE(s, pat, to) 正規表現で置換 8.0以降 ○
TRIM([from] s) 前後の文字を除去 ○ ○
LTRIM(s) / RTRIM(s) 左/右のスペース除去 ○ ○
UPPER(s) / LOWER(s) 大文字/小文字変換 ○ ○
LENGTH(s) バイト数 ○ 文字数を返す（注意）
CHAR_LENGTH(s) 文字数 ○ ○（CHARACTER_LENGTH）
LOCATE(sub, s) 部分文字列の位置（1始まり、0=未検出） ○ POSITION(sub IN s)
INSTR(s, sub) 部分文字列の位置（引数順が逆） ○ なし（POSITIONを使う）
LPAD(s, n, pad) 左側を文字で埋める ○ ○
RPAD(s, n, pad) 右側を文字で埋める ○ ○

よくある落とし穴 / 注意点

CONCATのNULL伝播

CONCATにNULLが混入すると結果全体がNULLになります。対処は2つあります。

-- CONCAT_WS: NULLをスキップしてセパレータで結合
SELECT CONCAT_WS(' ', sei, mei) FROM users;

-- IFNULL: NULLを空文字に変換してから結合
SELECT CONCAT(sei, ' ', IFNULL(mei, '')) FROM users;

CONCAT_WSはNULL列をスキップするため、余分なセパレータも入りません。氏名のように一方が欠けている場合はCONCAT_WSの方がすっきり書けます。

WHERE句での関数使用によるインデックス無効化

関数をWHERE句の左辺に使うと、そのカラムのインデックスが利用されません。

-- NG: email のインデックスが使われない
WHERE LOWER(email) = 'taro@example.com'

-- OK: 挿入時に小文字で正規化済みなら関数不要
WHERE email = 'taro@example.com'

検索性能が重要なカラムは、データ投入時にLOWER等で正規化しておくか、MySQL 5.7以降のGenerated Column（仮想カラム）にインデックスを貼る方法を検討します。

LENGTHとCHAR_LENGTHの混同

文字数の上限チェックには必ずCHAR_LENGTHを使います。LENGTHはバイト数を返すため、日本語テキストでは想定外の結果になります。PostgreSQLへ移植する際はLENGTHの意味が逆転する（文字数を返す）ことも覚えておきましょう。

まとめ

  • CONCATはNULLが1つでも混入すると結果がNULL。NULL列の結合にはCONCAT_WSかIFNULLを使う
  • SUBSTRING(str, pos, len)の位置は1始まり。LOCATEと組み合わせると動的な切り出しができる
  • REPLACEは大文字・小文字を区別してすべての一致箇所を置換する。正規表現での置換はMySQL 8.0以降のREGEXP_REPLACEを使う
  • TRIMは前後の余分な文字を除去する。CSVインポートやフォーム入力の整形に欠かせない
  • LENGTHはバイト数、CHAR_LENGTHは文字数。日本語を含むシステムでは両者を混同しないよう注意する
  • WHERE句の左辺に関数を使うとインデックスが無効化される。検索性能が重要なカラムは正規化して保存する

参考リンク

アイキャッチ画像: Photo by Jantine Doornbos on Unsplash

タイトルとURLをコピーしました