SQL練習室
← SQLガイド

SQLの文字列操作:連結・SUBSTRING・TRIM・REPLACEの使い方

SQLでは、文字列をつないだり、一部を取り出したり、余分な空白や区切り文字を取り除いたりできます。名前と商品コードを使い、元の値と加工後の値を並べて確認します。NULLや空文字がある場合の違いも見ていきましょう。

例で使う表

registrationsの1行は登録1件です。last_nameは姓、first_nameは名、product_codeは登録した商品コードです。姓・名がNULLの場合、名が空文字の場合、コードに前後の半角スペースがある場合を含めています。ID2のコードは先頭と末尾に各1個の空白があり、ID3のコードには内部の空白があります。表の空白も値の一部で、空文字は「''(空文字)」と表示します。ORDER BYなしの結果の行順は一例で、保証されません。

registrations:名前と商品コード5件
idlast_namefirst_nameproduct_code
1青木太郎AB-001
2北野NULL CD-002
3中央''(空文字)AB --003
4NULL花子日用品-004
5NULLNULLNULL

文字列の連結・切り出しの基本構文

sql
SELECT *, last_name || first_name, CONCAT(last_name, first_name)
FROM registrations;

||とCONCATで姓と名をつなぐ:NULLの扱いを比較する

||は左右の文字列を連結します。青木さんの姓と名は青木太郎になります。ただし、片方がNULLなら結果もNULLです。PostgreSQLのCONCATはNULLの引数を読み飛ばすため、名がNULLの北野さんは北野になります。空文字の名を持つ中央さんはどちらも中央です。両方NULLのID5は||ならNULL、CONCATなら空文字です。区切り文字は自動では入らず、必要なら引数に書きます。

sql
SELECT *, last_name || first_name, CONCAT(last_name, first_name)
FROM registrations;
実行結果
idlast_namefirst_nameproduct_code?column?concat
1青木太郎AB-001青木太郎青木太郎
2北野NULL CD-002 NULL北野
3中央''(空文字)AB --003中央中央
4NULL花子日用品-004NULL花子
5NULLNULLNULLNULL''(空文字)

SUBSTRINGで指定位置から文字を切り出す

SUBSTRING(product_code FROM 1 FOR 2)は、1文字目から2文字を取り出します。先頭は0ではなく1です。AB-001はAB、日本語の日用品-004は日用になります。位置と長さはバイト数ではなく文字数です。ID2は先頭に空白があるので、結果は空白とCの2文字です。この段階では前後の空白を削っていません。NULLのコードは結果もNULLです。

sql
SELECT *, SUBSTRING(product_code FROM 1 FOR 2)
FROM registrations;
実行結果
idlast_namefirst_nameproduct_codesubstring
1青木太郎AB-001AB
2北野NULL CD-002 C
3中央''(空文字)AB --003AB
4NULL花子日用品-004日用
5NULLNULLNULLNULL

TRIMで前後の空白を取り除く

TRIM(product_code)は、既定では前後の半角スペースを取り除きます。ID2の「 CD-002 」はCD-002になります。中央のハイフンや文字を削る処理ではありません。内部の空白も残ります。元のproduct_codeは変わらず、加工した値だけが追加されます。NULLを渡すと結果もNULLです。

sql
SELECT *, TRIM(product_code)
FROM registrations;
実行結果
idlast_namefirst_nameproduct_codebtrim
1青木太郎AB-001AB-001
2北野NULL CD-002 CD-002
3中央''(空文字)AB --003AB --003
4NULL花子日用品-004日用品-004
5NULLNULLNULLNULL

REPLACEで指定した文字列をすべて置き換える

REPLACE(product_code, '-', '')は、コード中のハイフンをすべて空文字に置き換えます。AB-001はAB001、ハイフンが2つあるAB --003もAB 003になります。ID2はハイフンだけが消え、前後の空白は残ります。置換先に空文字を指定すると、対象の部分文字列を削除できます。NULLのコードは結果もNULLです。

sql
SELECT *, REPLACE(product_code, '-', '')
FROM registrations;
実行結果
idlast_namefirst_nameproduct_codereplace
1青木太郎AB-001AB001
2北野NULL CD-002 CD002
3中央''(空文字)AB --003AB 003
4NULL花子日用品-004日用品004
5NULLNULLNULLNULL

文字列の連結・切り出しでよくある間違い

TRIMで内部の空白まで消えると思わない

TRIMは指定した文字を前後から取り除きます。TRIM(' A B ')の結果はA Bで、内部の空白は残ります。すべての半角スペースを消すならREPLACEで明示的に置き換えます。人名の区切りなど、残すべき空白まで消さないよう、加工の目的を決めます。

実際に1問解いてみる

||・CONCAT

全登録の全列の末尾に、姓と名を||でつないだ値、CONCATでつないだ値をこの順に追加してください。間に区切り文字は入れません。別名は付けず、行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

文字列の連結・切り出しについてよくある質問

SELECTで文字列を加工すると元の値も変わりますか?
変わりません。このページのSELECTは、検索結果に加工した値を表示しているだけです。元の姓・名や商品コードはそのまま残ります。
TRIMの結果列がbtrimと表示されるのはなぜですか?
この例のTRIM(product_code)は、PostgreSQLでは前後を取り除くbtrim関数に対応します。別名を付けない場合、結果の列名はbtrimです。||の連結式は?column?と表示されます。
TRIMでタブや全角スペースも消えますか?
既定で削るのは半角スペースです。タブや全角スペースなども対象にするなら、取り除きたい文字を明示します。この教材の前後の空白は半角スペースにそろえています。
NULLと空文字は同じですか?
PostgreSQLでは別です。NULLは値がないことを表し、空文字は文字が0個の値です。CONCATはNULLを飛ばしますが、||ではNULLがあると連結結果もNULLになります。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。