MySQL

mysqlで文字列を数値に変換して比較する

wordpressなどで金額をvarcharのカラムに20,000円という形で入れていた。
普通にmin()やmax()を使っていたら全く比較できていなかったのでまとめました。

データ

id fee
1 10,000円
2 20,000円
3 30,000円
4 5,000円

ダメな例

sql
SELECT
    min(fee),  // => 10,000 
    max(fee)   // => 5,000
  FROM posts

単純に文字列として比較されている為、 正常に集計できていなかった。

良い例

sql
SELECT
    min(abs(REPLACE(fee, ",", ""))) AS min_fee,  // => 5000 
    max(abs(REPLACE(fee, ",", ""))) AS max_fee   // => 30000
  FROM posts

置換でカンマを消してから abs()で絶対値を取得して、 min() max()をかけたらうまくいきました。
※カンマを消さないでabs()をかけるとカンマの前までしか絶対値を取得できないです。

また、データをもとの形(10,000円)で取り出したい時はこんな感じで書きました。

sql
SELECT
    concat(format(min(abs(REPLACE(fee, ",", ""))), 0), "円") AS min_fee,  // => 5,000
    concat(format(max(abs(REPLACE(fee, ",", ""))), 0), "円") AS max_fee   // => 30,000
  FROM posts

format()で3桁ごとに小数点を加えて、 concat()でを結合させて表示しました。

まとめ

基本的には数値は数値型のカラムに入れるべき。
文字列型に入れてしまった時は、数値に直して比較する。