たきるブログ

C#やOracleなどの情報を書いています。

【Oracle】数値をカンマ区切りにする

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

TO_CHAR - オラクル・Oracle SQL 関数リファレンス

ここにあるように、TO_CHARでOracleでカンマ区切りをしようとすると、指定した書式の桁数を超過すると、『#########』のように結果が返ってきてしまう。
つまり、予め表示する最大桁数が分からないとダメということだ。

最大桁数が分からないため、フォーマットに『9G999G999G999G999G999G999G』のように、推測で、結果が超過しない書式を指定することになる。
そのため、万が一結果が超過したら、『##########################』という結果が返り、システムとしては宜しくない可能性を残してしまう。
更に言うと、そうなった時、書式を見直す必要が出てくる。

そこで、TO_CHARは使うんだけど、Oracleだけで数値を適切な桁数でカンマ区切りにする方法を編み出した。

-- 数値のカンマ編集化
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

やってることは、指定された数値の桁数に応じて書式を生成しているだけ。
マイナス符号が入っていると、その分1組余計な書式を付与する可能性があるけど悪影響はないのでヨシとする感じ。
上記例では、小数部は2桁固定になっているが、ここはシステムが求める桁数に合わせる形になるだろう。

これは、案外重宝すると思う。
ただカンマ編集したいだけなのに、それをロジックでわざわざ書かなきゃいけないってのは案外面倒だしね。

Functionにするなどすれば、小数桁数の表現も変更可能だと思う。
でもこれにも欠点があって、OracleのNUMBER精度(11gでは38だった)を超えた数値の指定をすると正しい結果が得られない。

パッケージだとこんな感じ

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;
/