数値書式について

数値型のデータを文字列として出力する場合、数値書式を明示的に指定しないければ、RDBMSのデフォルトの数値書式で出力されます。

しかし、この数値書式が時々問題になることがあります。

一般的にはデータベースから数値のままデータを取得し、アプリケーション側で数値書式を設定することが多いので、データベース側で数値を文字列に変換することはあまりないのですが、もし、そのような処理を行っており、RDBMSのデフォルトの数値書式が問題になる場合は、数値書式を明示的に指定するなどして文字列に変換してやらなければなりません。

Oracleの場合

例えば、以下のような７桁（うち小数点以下２桁）の数値型の列を持つテーブルに

create table number_table ( col1 number(7,2) );

以下のような数値を登録し、

insert into number_table values (0);
insert into number_table values (0.01);
insert into number_table values (0.1);
insert into number_table values (1);
insert into number_table values (1.01);
insert into number_table values (1.1);
insert into number_table values (12.34);
insert into number_table values (123.45);
insert into number_table values (1234.56);
insert into number_table values (12345.67);
insert into number_table values (-12345.67);

以下のように数値書式を設定せずにそのまま検索すると、

select col1 from number_table;

以下のように該当する数値の有効桁数を保持するために十分な長さの文字列が返されます。

このような数値書式が問題になる場合には、TO_CHAR関数で数値書式を指定してやります。

例えば、以下の４通りの数値書式指定で検索してみると、

select 
	'"' || to_char(col1,'99999D99') || '"' "99999D99",
	'"' || to_char(col1,'99990D99') || '"' "99990D99",
	'"' || to_char(col1,'FM99990D99') || '"' "FM99990D99",
	'"' || to_char(col1,'FM99990D00') || '"' "FM99990D00"
	from number_table;

以下のような結果が返されます。

99999D99               99990D99               FM99990D99             FM99990D00
---------------------- ---------------------- ---------------------- ----------------------
"      .00"            "     0.00"            "0."                   "0.00"
"      .01"            "     0.01"            "0.01"                 "0.01"
"      .10"            "     0.10"            "0.1"                  "0.10"
"     1.00"            "     1.00"            "1."                   "1.00"
"     1.01"            "     1.01"            "1.01"                 "1.01"
"     1.10"            "     1.10"            "1.1"                  "1.10"
"    12.34"            "    12.34"            "12.34"                "12.34"
"   123.45"            "   123.45"            "123.45"               "123.45"
"  1234.56"            "  1234.56"            "1234.56"              "1234.56"
" 12345.67"            " 12345.67"            "12345.67"             "12345.67"
"-12345.67"            "-12345.67"            "-12345.67"            "-12345.67"

見てのとおり、数値書式はなかなかクセがあります。

故に、実際に数値書式を指定して出力する場合は、様々なパターンの値が実際にどのように出力されるかを十分確認してから利用したほうがいいでしょう。

なお、ここでは数値書式の要素として、'0'、'9'、'D'（小数点）、'FM'（空白を付けない）を使っていますが、これ以外によく使う要素としては'G'（桁区切り）、'L'（通貨記号）などがあります。

select to_char(col1, 'L99G990D00') "L99G990D00" from number_table;

L99G990D00
--------------------
               \0.00
               \0.01
               \0.10
               \1.00
               \1.01
               \1.10
              \12.34
             \123.45
           \1,234.56
          \12,345.67
         -\12,345.67

※'L'は常に11桁分の幅を取るため、上記の例では10桁の余分な空白が付きます。
　この空白を付けたくない場合は'FM'も指定する必要があります。

SQL Serverの場合

OracleのNUMBER型に相当するnumeric型を使って同様のテーブルを作成して試してみると、数値書式を指定しない場合は以下のようになります。

select col1 from number_table

col1      
--------- 
.00
.01
.10
1.00
1.01
1.10
12.34
123.45
1234.56
12345.67
-12345.67

Oracleで言えば'FM99999D00'に相当する。

そこで、数値書式を指定してみると．．．

と言ってもOracleほど細かく数値書式を指定する機能はないので、CAST関数を使って文字列に変換した場合と、STR関数を使って文字列に変換した場合の例を示します。

まずはCAST関数を使って文字列に変換してみると、以下のようになります。

select cast(col1 as varchar) from number_table
                               
------------------------------ 
0.00
0.01
0.10
1.00
1.01
1.10
12.34
123.45
1234.56
12345.67
-12345.67

この結果はOracleで言えば'FM99990D00'に相当します。

そして、STR関数を使って文字列に変換してみると、以下のようになります。

select str(col1,9,2) from number_table

--------- 
     0.00
     0.01
     0.10
     1.00
     1.01
     1.10
    12.34
   123.45
  1234.56
 12345.67
-12345.67

この結果はOracleで言えば'99990D99'に相当します。

その他、桁区切りを行うためにはmoney型やsmallmoney型というデータ型を使用しなければならないなど、SQL Serverの数値書式にはかなり制限が多い。

そのため、SQL Serverの数値書式設定機能にはあまり期待しないほうがいいでしょう。

Archive Redo Blog

DBエンジニアのあれこれ備忘録

数値書式について

Oracleの場合

SQL Serverの場合