PostgreSQLでSUBSTRING()で文字を取り出そう！(正規表現)
カラムの中から一部の値を取り出したいことってありますよね？
そんな時に便利なSUBSTRING()でカラムの中から好きな値を抜き取りましょう！
Contents
SUBSTRING()とは
SUBSTRING()は文字列の中から任意の文字列を取得することができる関数です。
SUBSTRING()の構文
SUBSTRING()には大きく分けて2つの構文があります。
文字列の場所を指定して取り出す構文
SUBSTRING ( 文字列orカラム, 開始位置, 長さ )
文字は1からカウントします。（Javaなどと異なり0始まりではないのでご注意を！）
※ 長さは省略可能です。
以下のように書くこともできます。
substring(文字列 from 開始位置 for 長さ);
どう書くかは好き好きです。
正規表現を使って取り出す構文
SUBSTRING(文字列, パターン);
「パターン」に正規表現を使用して抜き出したい文字のルールを作成します。
こちらも以下のように書くこともできます。
SUBSTRING(文字列 FROM パターン)
正規表現が使えると、かなり便利に値を取得できるようになります。
SUBSTRING()を使ってみよう！
動作環境
環境を作るのが面倒だったのでSQLiteonlineを使ってPostgreSQL9.6の動作確認を行いました。
開始位置を指定して取り出す
SELECT
SUBSTRING ( '123456789', 5, 2 ), -- 56
SUBSTRING ( 'haha', 5, 2 ), -- (空白)
-- 長さは省略可
SUBSTRING ( '123456789', 3 ) -- 3456789
正規表現で取り出す
正規表現はまだ苦手なので少しですが紹介します。
数値を取り出す
SELECT
SUBSTRING('123456789', '([0-9]{1,4})'), -- 1234
SUBSTRING('ニンバス2000', '([0-9]{1,4})'), -- 2000
SUBSTRING('スターウォーズ', '([0-9]{1,4})'), -- null
SUBSTRING('4次元ポケット', '([0-9]{1,4})'), -- 4
SUBSTRING('1_23_45678', '([0-9]{1,4})'), -- 1
SUBSTRING('A1B2', '([0-9]{1,4})'), -- 1
-- 数値の許容数を変えます。
SUBSTRING('1_2_3_4', '([0-9]{2,4})'), -- null
SUBSTRING('_1234', '([0-9]{0,4})') -- (空欄)
簡単な解説
([0-9]{1,4})')の[0-9]が数値{1,4}が抜き出す数を表しています。数値を1~4個(4桁まで)取り出してくれます。
ない場合はnullとなります。先頭からまとまった数値を取り出しています。{1,4}を{0,4}にすれば0が許容されるため最初に数値がなければ数値は取得しません。
アンダースコア周りを取り出す
SELECT
-- 前方の数値
SUBSTRING ( '1234_56789', '^[^_]*' ), -- 1234 (「 '(\d+)_' 」 でも同じ)
-- 2個目のアンダースコアまで
SUBSTRING ( '1234_567_89', '^[^_]*_[^_]*' ), -- 1234_567
-- 後方の数値(アンダースコア関係ないけど)
SUBSTRING ( '12345_6789', '[0-9]*$' ) -- 6789
簡単な解説
「^」(キャレット)は「以外」を表しています。「*」(アスタリスク)は直前の文字が0回以上発生するという意味です。「$」は後方一致を意味しています。
正規表現については以下でまとめています。
おわり
Substring()を使うと取り出したい値が取り出せて便利ですね。簡易テスト環境だとMySQLなど動かなかったのですが、いろいろ読んでみると正規表現が許容しているかはわかりませんが使えるようですね。