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序数接尾辞
Vn 桁シフト
EEEE科学技術表記法用の指数
postgresql.jpより抜粋

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

to_number() サンプルコード

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

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

- 文字から数値へ
SELECT TO_NUMBER('123,456', '999,999,999,999');

■ 結果

  to_number
1123456

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

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

SELECT TO_NUMBER('00123456', '000000000000');

  to_number
1123456

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

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

- 文字から数値へ
SELECT TO_NUMBER('123456', '9999');

■ 結果

  to_number
1123456

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

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

SELECT TO_NUMBER( '-12,345.6', '99G999D9' );

■ 結果

  to_number
1-12345,6000

負の記号は自動で変換してくれます。

例 小数点を含む数値変換 (9, G)

SELECT TO_NUMBER( '1234567.89', '999999999.9999' );

■ 結果

  to_number
11234567.89

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

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

SELECT TO_NUMBER( '1,234,567.89', '9G999g999' );

■ 結果

  to_number
11234567

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

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

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

■ 結果

  to_number
11234567.89

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

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

select to_number('123' || '456', '999999999999');

  to_number
1123456

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

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

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

■ 結果

  to_number
11234567

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

参考サイト

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

PostgreSQL, SQL

Posted by Nakamoto