[SQLite] CAST INTEGERで文字列から数値変換ができない

最近仕事でむちゃくちゃSQLiteを使っている。 スピードも遅いし、同時セッションによわよわだけど、SQLiteって何てよくできたツールなのかといつも関心してしまう。 SQLという言語を習得している人は、値の取り出しがもはやプログラミングであることを認識していると思いますが、初心者の人はなかなかこれが難しいようで、重い腰が上がらないようです。 SQLの秘訣は、JOIN、サブクエリ、UNIONを覚えたら、もはや敵無しですよ。（仕事で便利に使えるレベル） でも、SQLは、たくさん種類があって、共通記述と、その種類毎の方言がたくさんあって、それらを認識しておかないと、柔軟なSELECT文が書けないし、いろいろな言語コンフリクトも起きがちなんです。

SQliteでCASTを便利に使うために

個人的にSQLiteは、Mysqlに比べて重要な機能が少しだけ足りていない感があります。 例えば、文字列をsplitしたいという場面では、SUBSTRU()関数を使って、順番に文字列分解をしていく必要があるのが非常にめんどくさくて、 "1.2.3.4.5"の様な文字列で、"."（ドット）区切りの３番目の値を取り出したい場合は、Mysqlでは配列のように扱えるのに、SQLiteの場合は、左からドットを１つずつ検知してその前後の値を取得していく以外に今のところ手がありません。 他にもいくつか、めんどくさ場面はありますが、今回はCASTで困ったのでその話です。

実際にあった数値が文字列で登録されている問題

とあるシステム開発をしていた時に、システムで管理されたサーバーに置かれたフォルダ名を管理しているデータがありました。 ファイルというのは、"001/hoge.txt"のような感じでフォルダが３桁の数字で書かれていて、おそらく開発時にFTPソフトなどで見た目がソートされるようにこのゼロパディング３ケタ仕様にしている事が伺えます。 そして、データベースには、"001","002"という値が文字列で登録されていて、確かにこのままORDER BY（並び替え処理）をした時に、順番に並んでくれるのですが、 なんと、1000というフォルダが存在していました。 もちろん、次のような並びでFTPでは表示されています。

... 099 100 1001 101 102

なんということでしょう、４桁目でシステム的に破綻しているではないか！ もちろん、ORDER BYでも、上記の並びになってしまいます。 ということで、文字列を数値に変換する必要があるという必然に迫られてしまいました。 SQLにおける型変換は、自分の知る限り、CASTしかありません。

SQLにおけるCAST()関数

仕事で使っている環境のSQLiteでCASTしてみました。

失敗パターン

SELECT CAST("001" as INTEGER) as num これは結果がnullになります。 要するに変換できていないということ。 SELECT CAST("001" as REAL) as num こうすると、普通に、"001"が返ってきます。 なので、SQLiteでCAST関数が使えないというワケではなさそう。 そこで色々と調べてみて、文字列を数値変換できる方法を見つけました。

成功パターン

SELECT CAST("001" as FLOAT) as num こうすると、正常に、1という数値が返ってきました。 他にも数値化できる命令があって、 SELECT CAST("001" as DOUBLE) as num これでも1が返ってきます。 そう、整数値ではなく、小数値でなら正常にCASTされるということを発見しました。 これ書いているWEBサイトが見つからなかったので、困っている人も多いのではないかと想像しました。

CASTでdateが便利に使える

他にも、DATEというオペレータを使うと、 "2024/06/01"を"2024-06-01"という日付型の文字列に変換してくれてとても便利に使えることも分かりました。 ※詳しくは参考サイトを見てください。

あとがき

この問題、以前から気がついていたんですが、半ばあきらめてSQL操作をするPHPの側で型変換をしていたケースがありましたが、 今回の気付きによって、SQLite内で解決することができるようになりました。 人類にとって大きな一歩でしたね（自分が知らんかっただけかもだけど） とにかく、こうやって自分の知見が広がっていくことはうれしくもあり、楽しい事でもありますね。

参考サイト

[SQLite] CASTで型変換