Help us understand the problem. What is going on with this article?

Oracleだけで数値をカンマ区切りにする

More than 1 year has passed since last update.

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文でごちゃごちゃしてるのも何なので、パッケージとかファンクションとかにしちゃう。

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;

    RETURN TRIM(TO_CHAR(A_VALUE, LPAD('999', 3 + (CEIL((LENGTH(TRUNC(A_VALUE)) - 3) / 3) * 4), '999G') || L_DECIMAL_FORMAT));
  END;

END;
/
SQL
-- 数値のカンマ編集化
SELECT
    PKG_NUMBER.TO_FORMAT(1000000000000)
  , PKG_NUMBER.TO_FORMAT(1000000000000.25, 2)
FROM
    DUAL

注意

  • マイナス符号が入っていると、その分1組余計な書式を付与する可能性があるけど悪影響はないと思います。
  • OracleのNUMBER精度(11gでは38だった)を超えた数値の指定をすると正しい結果が得られません。
Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away