MariaDB講座 / 文字列を結合・抽出・置換し、長さを調べる
文字列関数は文字を結合・抽出・置換した結果や長さを返します。CONCAT、SUBSTRING、REPLACE、CHAR_LENGTHを目的に合わせて選び、NULLと空文字、文字数とバイト数を分けて扱います。
加工の目的と保存値を分ける
名前と分類をつなぐ、コードを切り出す、電話の区切りを消すのは異なる操作です。SELECT内の関数は出力を計算するだけで、保存名を自動更新しません。文字列の型と欠損値を確認し、何を原本として残すか決めます。
CONCATは通常モードで引数にNULLがあるとNULL、CONCAT_WSは指定した区切りでつなぎ、NULLを飛ばします。ただし空文字は飛ばしません。SUBSTRINGの先頭は位置1、REPLACE関数は置換文字列を返すもので、行を置き換える同名のSQL文とは別です。
表示用ラベルを作る
course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で環境と同名表を確認します。通常例をUTF-8ファイルに保存してSOURCEで実行できます。既存の同名表は用途・定義・行を調べ、不要と確認できるまでは置き換えません。
USE shopcourse; CREATE TABLE products_19_lab ( product_id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, category VARCHAR(30) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, note VARCHAR(100) NULL DEFAULT NULL, PRIMARY KEY (product_id), CONSTRAINT ck_p19_price CHECK (price >= 0), CONSTRAINT ck_p19_stock CHECK (stock >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO products_19_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL), (2, '볼펜', '문구', 1500.00, 0, ''), (3, '머그컵', '생활', 8000.00, 12, '선물 포장'), (4, '스탠드', '생활', 25000.00, 5, NULL), (5, '파일', '문구', 1500.00, 50, ''), (6, '케이블', '전자', 8000.00, 0, '호환 확인');
SQL内の韓国語サンプルは文字コードと結果を原文にそろえたものです。ID1～6はノート、ペン、マグカップ、ライト、ファイル、ケーブル、分類は文具・生活用品・電子用品です。数値とIDで結果を比較します。noteは1・4がNULL、2・5が空文字、他は記入済みで、在庫は20・0・12・5・50・0です。
SELECT product_id, CONCAT(product_name,' / ',category) AS label,
CONCAT_WS(' / ',product_name,note) AS note_label
FROM products_19_lab ORDER BY product_id;
ID1のlabelは名前と文具分類をスラッシュで結合し、note_labelはNULLを飛ばして名前だけになります。ID2は空文字が残るため区切りも残ります。表示を整えるためだけに保存された空文字をNULLへ変えず、先に表示方針を決めます。
位置で抽出し区切りを置換する
固定文字列で試します。
SELECT SUBSTRING('MariaDB',1,5), SUBSTRING('MariaDB',-2);
SELECT REPLACE('010-1234-5678','-','') AS normalized_phone;
結果はMaria、DB、01012345678です。SUBSTRINGは1始まりで、負の位置なら末尾から数えます。文字単位で扱えますが、業務コードが本当に固定位置を持つか確認してから使います。
電話を数値へ変えると先頭0や国番号の記法などを失い得ます。記号を消すだけでは識別子の正規化仕様は完成しません。
文字とバイトを比較する
utf8mb4の接続で実行します。
SELECT CHAR_LENGTH('한글'), LENGTH('한글'),
CHAR_LENGTH('🙂'), LENGTH('🙂');
韓国語二文字は文字数2・バイト数6、絵文字は1・4です。見た目の一文字が複数コードポイントになる場合もあり、CHAR_LENGTHが常に表示上の一文字を数えるわけではありません。入力100文字と通信100バイトは別の条件です。UTF-8を任意のバイトで切ると不正な列になる場合があります。
NULL・空文字・空白除去を分ける
同じ入力の扱いを比較します。
SELECT CONCAT('노트',NULL),
CONCAT_WS(' / ','노트',NULL,'');
SELECT CONCAT('[',TRIM(' 노트 '),']');
CONCATの最初の結果はNULLです。CONCAT_WSは空文字を残すので末尾に区切りが付き、TRIMは例の前後の普通の空白を除きます。すべてのUnicode空白、語中の空白、不可視文字まで除く機能ではありません。名前やパスワードの空白が意味を持つこともあります。
大文字小文字やアクセントの比較が問題なら、別の加工関数で解決したと思わず照合順序も調べます。UPDATEへ適用する前に、主キー・元の値・加工結果をSELECTで並べ、対象と復旧手順を確認します。
再実習では選択中のDB、定義、保存行を確認し、必要なものを保存した後、削除可能なこの記事の実習表だけを削除します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。他の記事や実アプリケーションの表へ範囲を広げないでください。
DROP TABLE shopcourse.products_19_lab;
