XXX.XXX.XXX形式のVersionをソート時に気をつけたほうがいいこと

今回言いたいこと

「.」付き、つまり「文字列のバージョン」をソートをする場合は気をつけましょう！

経緯

DBの項目にversionを持たせているのですが、想定通りにソートされないため対応しました。

原因は、文字列である「.」を含めた文字列でバージョン管理をしていたためでした。

例）
1.22.0
1.3.0
の値かつ昇順で並べた場合、
本来は、1.3.0 > 1.22.0となって欲しいのですが
文字列の2番目の要素である"2"と"3"を比較して2が勝つため
1.22.0 > 1.3.0
と想定しないソートとなっていました。

解決方法

PostgreSQLで対応

対応したSQL

select 
    verion,
from 
	items 
ORDER BY
	LPAD(SPLIT_PART(SUBSTRING(regexp_replace(verion, '[Xx]', '0', 'g') FROM '[0-9.]+$'), '.', 1), 3, '0'),
	LPAD(SPLIT_PART(SUBSTRING(regexp_replace(verion, '[Xx]', '0', 'g') FROM '[0-9.]+$'), '.', 2), 3, '0'),
	LPAD(SPLIT_PART(SUBSTRING(regexp_replace(verion, '[Xx]', '0', 'g') FROM '[0-9.]+$'), '.', 3), 3, '0');

解説

補足)　selectに、ORDER BYの値を入れると動きが分かりやすいですので、解説がわかりにくい方は値を入れてみるといいかもです。

1. Xを置換

regexp_replace(verion, '[Xx]', '0', 'g'),

私が対応した案件の仕様で
未確定のバージョンに対しては、「X」を入れることができます。

そのため、Xを0に強制的に置き換えました。

場合によってはこちらの処理はなくても良いです。

2. 不要な文字列を削除

SUBSTRING([1.の部分] FROM '[0-9.]+$')

先頭に、「v」がついてるデータが混じっていたため無理やり削除する処理です
こちらも仕様独特なので、場合によってはなくても良いです。

例） v1.2.3 → 1.2.3

3. 「.」区切りで値を取得する

SPLIT_PART([2.の部分], '.', 1)

[2.の部分]を、「.」で区切り、その1番目の値を返します。

例）1.2.3 → 1を返す

以降、2番目、3番目と値を返します。

SPLIT_PART([2.の部分], '.', 2)

例）1.2.3 → 2を返す

SPLIT_PART([2.の部分], '.', 3)

例）1.2.3 → 3を返す

＊ 今回の対応は、3つの「.」区切りまで対応してます。
X.X.X.Xみたいに４つにしたい場合は、さらに増やしてください。

4. 0埋めしてあげる

LPAD([3.　の部分], 3, '0')

3桁までとして、足らない部分は0埋めします。越えた分は、、、切り捨てます

例）1.2.3 → 「001,002,003」

5.以上の処理のORDER BYで順にソートしてあげる

上記処理を順番に、1番目、2番目、3番目と比較することで正常にソートができるようになりました。

例）
1.22.0　→ 「001,　022,　000」
1.3.0　→　「001,　003,　000」

1番目から順番に比較していく。2番目の要素が、22と3で22の方が値が大きいと判断して、
想定通り、1.3.0 > 1.22.0 の順でソートされるようになる

参考URL

とても参考になりました。
https://shobon.hatenablog.com/entry/2018/02/13/202859