文字列関数
名前は合っているのに、検索が 0 件になる
contacts テーブルから「株式会社ゆめさく」を探したのに 0 件でした。よく調べると、取り込み元の CSV の末尾に空白が 1 つ入っていて、保存されている値は「株式会社ゆめさく 」でした。
人の目には同じでも、データベースにとっては別の文字列です。前後の空白を落とす TRIM を通せば、比べられる形になります。
SQL クエリ
SELECT id, TRIM(company_name) AS company
FROM contacts
ORDER BY company;空白は、表計算からの貼り付けや外部システムからの取り込みで簡単に混ざります。自分たちで作っていないデータを扱うときは、まず TRIM を疑うくらいでちょうどよいです。
Yamada@ と yamada@ は別の文字列
大文字と小文字も、そのままでは別物です。同じ人が「Yamada@example.com」と「yamada@example.com」で 2 回登録できてしまう、という形で表に出ます。
LOWER を通すと全部小文字に、UPPER なら全部大文字に揃います。
SQL クエリ
SELECT id, mail_address
FROM contacts
WHERE LOWER(mail_address) = 'yamada@example.com';要点は、比べる両側を同じ形に揃えることです。片側だけ小文字にしても意味がありません。右辺に大文字の混じった文字列を書いてしまうと、やはり 1 件も返らなくなります。
コードの先頭 3 文字で分類したい
商品コードが ELC-1024 のように「分類の記号 + 連番」で作られていることがあります。分類の列が用意されていなくても、先頭の 3 文字を切り出せば、それがそのまま分類のキーになります。
SQL クエリ
SELECT
code,
SUBSTRING(code FROM 1 FOR 3) AS category_code
FROM items
ORDER BY category_code, code;SUBSTRING(列 FROM 開始位置 FOR 長さ) と書きます。位置は 1 文字目から数えます。取り出した値は並べ替えにも集計のキーにも使えるので、あとから分類の列を足さなくても分けて数えられます。
現場の話 見た目を整えるだけなら、アプリ側でやったほうが融通が利きます。SQL の文字列関数が要るのは、整えた値で「比べたい」「まとめたい」ときです。整形した結果を
WHEREや集計のキーに使うかどうかが、どちら側で処理するかの目安になります。
テーブル構造
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(80) NOT NULL
);
INSERT INTO users (id, name, email) VALUES
(1, '田中太郎', ' Taro.Tanaka@Example.com '),
(2, '佐藤花子', 'HANAKO.SATO@example.com'),
(3, '鈴木一郎', ' ICHIRO@example.COM'),
(4, '高橋美咲', 'misaki.takahashi@EXAMPLE.com '),
(5, '伊藤健太', 'KENTA.ITO@example.com');