Oracleだけで数値をカンマ区切りしたい
と思うのは私だけかもしれませんが・・・。
特定の環境下において、ロジックでカンマ区切りを行うと、パフォーマンスを損ねてしまう場合に有効だと思います。
DBから何千件もSELECTするのに、カンマ区切りにするのに件数分ループしなくてはならなくて、更に画面出力するにも件数分ループしなければならない、という。
実装設計の問題だのという話はとっくに超越した次元の時。
OracleのTO_CHAR
TO_CHAR - オラクル・Oracle SQL 関数リファレンス
ここにあるように、TO_CHARでOracleでカンマ区切りをしようとすると、指定した書式の桁数を超過すると、『#########』のように結果が返ってきてしまう。
つまり、予め表示する最大桁数が分からないとダメ。
最大桁数が分からないため、フォーマットに『9G999G999G999G999G999G999G』のように、推測で、結果が超過しない書式を指定することになる。
その都度、最大桁数を意識しなければならないというのは正直めんどくさい。
クエリで頑張る
整数部の桁数を勝手に判断したフォーマットする。
'D00'の部分は表現したい小数桁数に合わせる。
SQL
SELECT
TRIM(TO_CHAR(1000000000000, LPAD('999', 3 + (CEIL((LENGTH(1000000000000) - 3) / 3) * 4), '999G')))
, TRIM(TO_CHAR(1000000000000.25, LPAD('999', 3 + (CEIL((LENGTH(TRUNC(1000000000000.25)) - 3) / 3) * 4), '999G') || 'D00'))
FROM
DUAL
結果はこうなる。
1,000,000,000,000 1,000,000,000,000.25
やってることは、指定された数値の桁数に応じて書式を生成しているだけ。
パッケージで頑張る
SQL文でごちゃごちゃしてるのも何なので、パッケージとかファンクションとかにしちゃう。
2022/05/27 追記
値が1未満の場合、[.99]という結果になってしまうので、[0.99]となるように修正しました。
(SQLだけで頑張るのはかなりとんでもないことになりそう・・・)
PKG_NUMBER
CREATE OR REPLACE PACKAGE BODY PKG_NUMBER
IS
/**
* カンマ区切りにした数値文字列を取得します。
* @param A_VALUE 数値
* @param A_DECIMAL_NUM 小数桁数
* @return カンマ区切りにした数値文字列
*/
FUNCTION TO_FORMAT(A_VALUE NUMBER, A_DECIMAL_NUM NUMBER := 0)
RETURN VARCHAR2
IS
L_DECIMAL_FORMAT VARCHAR2(100);
BEGIN
L_DECIMAL_FORMAT := '';
IF A_DECIMAL_NUM > 0 THEN
L_DECIMAL_FORMAT := 'D' || LPAD('0', A_DECIMAL_NUM, '0');
END IF;
IF A_VALUE >= 1 THEN
RETURN TRIM(TO_CHAR(A_VALUE, LPAD('999', 3 + (CEIL((LENGTH(TRUNC(A_VALUE)) - 3) / 3) * 4), '999G') || L_DECIMAL_FORMAT));
ELSE
RETURN TRIM(TO_CHAR(A_VALUE, '0' || L_DECIMAL_FORMAT));
END IF;
END;
END;
/
SQL
-- 数値のカンマ編集化
SELECT
PKG_NUMBER.TO_FORMAT(1000000000000)
, PKG_NUMBER.TO_FORMAT(1000000000000.25, 2)
FROM
DUAL
注意
- マイナス符号が入っていると、その分1組余計な書式を付与する可能性があるけど悪影響はないと思います。
- OracleのNUMBER精度(11gでは38だった)を超えた数値の指定をすると正しい結果が得られません。