SQLで同じカラムに入っている文字列から特定の箇所を抽出する

Question

以下のようなテーブルがあり、

id	job
1	【資格1】月給100,000円【資格2】月給105,000円＋賞与 基本給2ヶ月分【資格3】月給120,000円＋賞与 基本給2ヶ月分 手当…6,000円/月

このテーブルから、資格2の月給はいくらか、賞与はいくらかの情報のみを抜き出したいです。
可能でしょうか。よろしくお願いいたします。

Comments

[sazi]

「賞与はいくらか」というのは例でいうと「基本給2ヶ月分」とかですか？また、パターンとしては「賞与」の文字に続く以外に法則はあるのでしょうか

[9nom]

パターンとしては、「賞与」の文字に続くものしかありません。その他の文字もパターンはこれのみとなっています。

[sazi]

【資格2】と【資格3】に挟まれているもの、という抽出条件は要件を満たしますか？

[9nom]

満たします。(応用すれば資格1と資格2の間に挟まれているもの、も抽出できるので)

Answer 1

こんにちは！
saziさんのおっしゃる通り、正規表現(パターンマッチ)が使えます。

select 
 job,
 substring(job,'【資格2】月給([\d,]+)円') as 基本給,
 substring(job,'【資格2】月給[\d,]+円＋賞与 基本給(\dヶ月分)') as 賞与
 from table 

job	基本給	賞与
【資格1】月給100,000円【資格2】月給105,000円＋賞与 基本給2ヶ月分【資格3】月給120,000円＋賞与 基本給2ヶ月分 手当…6,000円/月	105,000	2ヶ月分

posglesqlのバージョンは9.3で確認しました。

Comments

[yambejp]

「.*」っていります？

[mikupedia]

ご指摘ありがとうございます。
不要なので削除しました。

Answer 2

色々取り出し方は考えられます。（文字列関数と演算子）
「【資格2】と【資格3】に挟まれているもの」で抽出

select 
  replace(replace(substring(job from search1 || '.+?' || search2), search1, ''), search2, '') -- 正規表現
, substring(job, position(search1 in job)+length(search1), position(search2 in job)-position(search1 in job)-length(search2))
, split_part(split_part(job, search2, 1), search1, 2)
from (values(
  '【資格2】'
 ,'【資格3】'
 ,'【資格1】月給100,000円【資格2】月給105,000円＋賞与 基本給2ヶ月分【資格3】月給120,000円＋賞与 基本給2ヶ月分  手当…6,000円/月'
 )) as test(search1,search2,job)

正規表現では直接抜き出しも表現で可能だと思いますが、得意ではないので。。。

Answer 3

賞与、ボーナス、複数の言い方がありそうな？

使い物になるシステムを作りたいなら、データベースの正規化 をきちんと理解した上でテーブルを設計しては？

Comments

[9nom]

文言の言い回しのパターンは現行のもののみとします。

[9nom]

テーブルがどうなっているかはこの問題(すでにこの状態になってしまっているものを名寄せするタスク)には関係ないことです。

[Orlofsky]

strpos で[賞与]と[円]の位置を特定して、substrで抜き出すことになります。[賞与]が複数あったり、ひとつもなかった時の例外をどうするかは考えてください。
https://www.postgresql.jp/document/9.6/html/functions-string.html#functions-string-sql

Answer 4

これってSQLのやることではないのでは？
もしやるならそれなりのテーブルを組んでおく必要があると思います

参考

今回はpostgreのご質問のようですがmysqlだとjson型のカラムを設定できますので
同じような処理がpostgreにもあるかもしれません。

/* あくまでmysql */
create table tbl (id int,job json);
insert into tbl values(1,'{"資格1":{"月給":"100,000円"},"資格2":{"月給":"105,000円","賞与":"基本給2ヶ月分"},"資格3":{"月給":"120,000円","賞与":"基本給2ヶ月分","手当":"6,000円/月"}}')

資格2の月給を調べる

SELECT job->"$.\"資格2\".\"月給\"" FROM tbl