PostgreSQLで文字列を数値に変換 to_number()を使ってみる! SQL超初心者の勉強

SQLで文字列を数値に変換するときに使う、to_number(string, format)。
右側には変換するパターン（format）を書くのですが、書き方がよくわからなかったのでまとめてみました。

to_number()について

構文

TO_NUMBER(string, format)

フォーマットパターン

このパターンを使ってフォーマットを作成します。

パターン	説明
9	指定された桁数での値
0	前にゼロが付いた値
.（ピリオド）	小数点
, (コンマ)	千単位で区切る符号
PR	負の値の角括弧表示
S	符号付き値（ロケールを使用）
L	通貨記号（ロケールを使用）
D	小数点（ロケールを使用）
G	グループ区切り文字（ロケールを使用）
MI	（数値 < 0であれば）指定位置にマイナス記号
PL	（数値 > 0であれば）指定位置にプラス記号
SG	指定された位置にプラス/マイナス記号
RN	ローマ数字（入力は1～3999）
THまたはth	序数接尾辞
V	n 桁シフト
EEEE	科学技術表記法用の指数

フォーマットの書き方の表をみても書き方がなぞです。
サンプルコードを見ながら読み解きたいと思います。

to_number() サンプルコード

上記フォーマットからよく使いそうなサンプルコードを作成しました。

例 文字から数値へ変換(9)

– 文字から数値へ
SELECT TO_NUMBER(‘123,456’, ‘999,999,999,999’);

■ 結果

	to_number
1	123456

※フォーマットなのでそれより少ない数値で変換しても問題ないです。

文字から数値へ変換 (0)

– 0を含めた文字から数値へ
SELECT TO_NUMBER(‘00123456’, ‘000000000000’);

	to_number
1	123456

※フォーマットなのでそれより少ない数値で変換しても問題ないです。
0も含めた桁数0が必要です。

例 文字から数値へ変換 (桁数が足りない時)

– 文字から数値へ
SELECT TO_NUMBER(‘123456’, ‘9999’);

■ 結果

	to_number
1	1234

右側がその分けずられます。0でも同じです。

例 文字から数値へ変換(9, g, d)

SELECT TO_NUMBER( ‘-12,345.6′, ’99G999D9’ );

■ 結果

	to_number
1	-12345.6

負の記号は自動で変換してくれます。小数点はdよりピリオド、区切り文字はgよりカンマの方がのほうがわかりやすい気がしますね。

例 小数点を含む数値変換 (9, ピリオド)

SELECT TO_NUMBER( ‘1234567.89’, ‘999999999.9999’ );

■ 結果

	to_number
1	1234567.89

適当な位置にドットを配置、左右数値より9が多ければ削られずに数値へ変換される。

例 小数点以下切り捨て (9, G)

SELECT TO_NUMBER( ‘1,234,567.89’, ‘9G999g999’ );

■ 結果

	to_number
1	1234567

例 通貨から数値へ (L, 9, G, . )

通貨の記号はロケール(国とか地域)に合わせます。

SELECT TO_NUMBER( ‘$1,234,567.89’, ‘L9G999g999.99’ );

■ 結果

	to_number
1	1234567.89

ロケールがあっていないと、234567.89となってしまいます。

例 複数値を1つの数値へ (9 と||演算子)

select to_number(‘123’ || ‘456’, ‘999999999999’);

	to_number
1	123456

|| は文字を連結する時に使う演算子です。

例 記号を含む文字列を数値へ

SELECT TO_NUMBER( ‘123-456*7’, ‘999999999’ );

■ 結果

	to_number
1	1234567

とくに何もせずとも、読み込んでくれました。

参考サイト

https://www.postgresqltutorial.com/postgresql-to_number/