Redshiftに格納されている文字列データから特定の文字に囲まれた文字列を抜き出す方法

はじめに

こんにちは、株式会社ジールの@Suguru-Terouchiです。
今回はRedshiftに格納されている文字列データから特定の文字に囲まれた文字列を抜き出す方法について記載したいと思います。

やりたきこと

「xxx-12345678-xxx」というようなデータがあった場合に「12345678」だけを抜き出したい。

Redshiftでは POSITION関数 を使うことで文字列中にある任意の文字を検索して、その位置を取得することが出来ますが、
今回のデータの場合、１つ目の「-」の位置を検索してから SUBSTRING関数 を使って、
「xxx-12345678-xxx」から１つ目の「-」以降の文字列「12345678-xxx」を抜き出し、また「-」の位置を探して、、と煩雑になってしまい、
可読性、保守性にも残念なSQLを書くはめになってしまいます。

残念なSQLの例：

select substring(substring('xxx-12345678-xxxx', position('-' in 'xxx-12345678-xxxx') + 1), 1, position('-' in substring('xxx-12345678-xxxx', position('-' in 'xxx-12345678-xxxx') + 1)) - 1)

はい、もう見る気なくしますね。。。
これを作るのに小一時間を無駄にしたくないアナタ（過去の自分）に良い方法を伝授したいと思います。

特定の文字に囲まれた文字列を抜き出す方法

前置きが長くなりましたが、早速、本題の方法について記載していきたいと思います。
今回はRedshiftで使用可能な文字列関数から REGEXP_SUBSTR関数 で正規表現を使ってズバッと狙った文字列を抜き出してみせたいと思います。

select regexp_substr('xxx-12345678-xxxx', '\-(.*)\-')

実行結果
-12345678-

うーん、、ちょっと違いますね、この辺で既に正規表現を使えない人間だとバレてしまっているかも知れませんが、
気を取り直して、再トライしてみます。

select regexp_substr('xxx-12345678-xxxx', '\-(.*)\-', 1, 1, 'e')

実行結果
12345678

狙い通りの文字列を抜き出すことに成功しました。
正規表現パターンを表す文字列リテラルに（）がありますが、そこが上手く認識されていなかったようです。
今回のケースでは「e」というパラメータを引き渡してあげることで想定していた結果を得られました。

めでたしめでたしと行きたいところですが、
ヒゲを生やした意地悪な 素晴らしい上司が「その方法で全てのデータパターン対応できているの？」と言ってきました。
「大丈夫ですよ！」と言いつつも不安を覚え、こっそりテストをしてみると案の定、対応しきれていないパターンがありました。

それは「xxx-12345678-0000-xxx」というようなデータがあった場合に上記のSQLでは「12345678-0000」という結果が返ってきてしまいました。
なにか対応策がないか試行錯誤しているうちに以下のSQLに辿り着きました。

select regexp_substr('xxx-12345678-0000-xxxx', '\-(.*?)\-', 1, 1, 'ep')

正規表現の世界では「*」のあとに「?」を入れることで非貪欲なマッチングにすることが出来ることはすぐに分かったのですが、
１つ前のSQLに「?」を追加するとSQLでエラーが発生してしまい、失意のどん底に突き落とされてしまいました。

諦めずにもう一度、関数のマニュアルを眺めていると、パラメータの最後に「p」の説明を見つけ、
藁にも縋る思いでパラメータに追加したところ、無事に想定した結果を得ることが出来ました。

参考URL

https://atmarkit.itmedia.co.jp/ait/articles/2103/16/news025.html
http://taustation.com/python3-regex-greedy-and-non-greedy/
https://qiita.com/ren0826jam/items/896bd8b49d154808d61f

まとめ

いかがでしたでしょうか。
最後のパラメータ「p」について説明をするスキルがなく、詳しい方コメントなど頂けると嬉しいです。

これまでも仕事で正規表現を使う機会は何度かありましたが、
毎回忘れてハマってしまうのでこの機会にTipsとして残せればと思い執筆に至りました。
（ただ正規表現とは今後も仲良くなれる自信はありません。。。）

ご参考になれば幸いです。

データ活用をサクッと始めるなら！

株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：