ビジネスにおいて、桁数を0で埋めるゼロパディング処理した数字が使われることがあり、これをSQLで出力することも可能です。
ただし、OracleやSQL Serverなどのデータベース管理ソフトによっては、SQLの書き方が違うので簡単に移植できないことも。そこで、今回は桁数を0で埋める処理の基本と、データベース管理ソフトの違いについて解説します。
そもそもゼロ埋めした数字とは
ゼロ埋めした数字とは、指定された桁数分、数字の左側に0を並べた数字のことです。このゼロ埋めした数字を、数量や金額の表示に使うことはありませんが、数字を使ったIDではよく使われる表現方法です。次から、一般的なゼロ埋めの数字の作り方について説明します。
ゼロ埋めした数字の例
例えば、SQLのテーブルでプライマリーキーとして使われるIDを作る場合、数字だけで作るケースもありますが、商品コードなどでは桁数が固定の数字が使われます。具体的には、6桁の数字を使う場合、6桁未満の数字では「000001」といった具合に0で埋めた数字を使うのが一般的です。
そして、このような桁数が決まった数字を表現する際、数字が存在しない桁をゼロで埋めた数字を、ゼロ埋めした数字と言います。
例えば、6桁のゼロ埋めした数字は、次のようになります。
6桁のゼロ埋めした数字の例
000001
000012
000123
001234
012345
123456
ゼロ埋めの数字を作る方法
ゼロ埋めの数字は、Excelやプログラム言語などで簡単に作ることが可能です。やり方はいろいろありますが、解りやすいゼロ埋めの数字を作る方法としては、次のような手順があります。
まず、対象の数字を文字列に変換します。そして、元の数字の桁数を調べて、桁数に応じた数のゼロを、その文字列の左側に結合します。
次に紹介する方法が、6桁の数字からゼロ埋めの数字を作る処理です。
もし、数字が10より小さいなら、”00000″ と結合する。例 “00000” + “1” = “000001”
もし、数字が10以上100より小さいなら、”0000″ と結合する。例 “0000” + “12” = “000012”
もし、数字が100以上1000より小さいなら、”000″ と結合する。例 “000” + “123” = “000123”
もし、数字が1000以上10000より小さいなら、”00″ と結合する。例 “00” + “1234” = “001234”
もし、数字が10000以上100000より小さいなら、”0″ と結合する。例 “0” + “12345” = “012345”
もし、数字が100000以上なら、そのまま文字列にする。例 “123456”
プログラム言語では書式指定でも可能
先ほど、数字を文字列に変換してゼロ埋めの数字を作る方法を紹介しましたが、多くのプログラム言語では、そのような面倒なことはしません。よく使われるのは、書式指定で作る方法です。例えば、C言語では、次のように書きます。
C言語でゼロ埋めの数字を作る方法
sprintf("%06d", number);
上記の例では、書式指定が可能なsprintf関数で、６桁の数字を指定する「%6d」に、さらに、空白をゼロ埋めを指定するため「%06d」として指定しています。
SQLでは、このような書式指定で文字列に変換する方法は使えませんが、ゼロ埋めの数字を作る関数を使って変換できます。
SQLでゼロ埋めの数字を作る方法
プログラム言語では、数字を文字列に変換すれば先ほど説明したような方法で、ゼロ埋めの数字を作ることが可能です。なお、プログラム言語によっては、組み込み関数を使うことで、もっと簡単に作ることもできます。では、SQLで作るにはどうすればよいのでしょうか。次から、SQLでゼロ埋めの数字を作る方法について紹介します。
SQLのゼロ埋めの数字を作る関数
SQLには、文字列を扱う関数が幾つもありますが、その中にゼロ埋めの数字を作る関数があり、それがLPAD関数です。そして、LPAD関数の構文は、次のとおりです。
LPADの基本
LPAD( 文字列, 文字数, 左詰めする文字 )
LPADを使った例 SELECT LPAD( num, 6, '0') FROM table1;
上の例は、テーブル「table1」に格納されているnum列の数字を、6桁で0を左詰めした文字列を表示します。
なお、LPAD関数の第1引数に指定する列名は、文字列型のものを使うのが基本です。数字型の列を使っても動作するデータベース管理ソフトがありますが、PostgreSQLなどでは正しく動作しないので、SQLを移植する際には注意してください。
LPAD関数の使用例
先ほど、LPAD関数の使い方と簡単な使用例を紹介しましたが、LDPA関数はSQLの文法さえ守っていれば、SQLのどこででも使えます。そのため、先ほどの例のようなSELECT句に続いて使うことも可能ですし、WHERE句に続く条件として利用することもできます。
LPAD関数をWHERE句に使った例 SELECT A.ID, A.KEYWORD, B.NAME FROM A, B WHERE A.KEYWORD = LPAD( B.NUM, 6, '0' );
上の例では、AとBの2つのテーブルを使い、テーブルBのNUM列からゼロ埋めの数字を作り、テーブルAのKEYWORDに一致しているデータのみ出力します。
ゼロの代わりに「*」を使うことも
桁数が解るように数字を表示する方法は、ゼロ埋めだけではありません。ゼロの代わりにアスタリクスが使われるケースもあります。
アスタリクスで埋めた数字の例 *****1 ****12 ***123 **1234 *12345 123456
これをSQLで実現する場合は、ゼロの代わりに「*」を指定します。
アスタリクスで埋めた数字を出力するSQLの例 SELECT A.ID, A.KEYWORD, B.NAME FROM A, B WHERE A.KEYWORD = LPAD( B.NUM, 6, '*' );
FORMAT関数を使う方法
先ほど、標準SQLに基づくゼロ埋めのやり方を紹介しましたが、データベース管理ソフト独自コマンドが使えるケースもあります。具体的には、マイクロソフト製のSQL Serverで使えるFORMAT関数です。次から、このFORMAT関数の使い方を紹介します。
マイクロソフト製品はFORMAT関数を使う
マイクロソフト製のデータベース管理ソフトでは、ゼロ埋めの数字の作成に、FORMAT関数を使います。なお、マイクロソフト製のデータベース管理ソフトとしては、SQL Serverが有名ですが、AccessでSQLを使う際もゼロ埋めはFORMAT関数であり、クラウドで利用できるAzure SQL データベースもゼロ埋めはFORMAT関数です。
FORMAT関数の使い方
FORMAT( 数字, 出力フォーマット )
FORMAT関数で指定できる出力フォーマットは、.NET Framework 形式文字列です。数字をゼロ埋めする例を次に紹介します。
FORMAT関数の例 SELECT FORMAT(1,'D4') SELECT FORMAT(1,'0000')
上の例の出力は、どちらも「0001」です。
移植時の注意点
よく使われるデータベース管理ソフトの中で数字のゼロ埋めにFORMAT関数を使うのは、マイクロソフト製品だけです。そのため、Oracle DatabaseなどからSQLを移植する場合は、LPAD関数をFORMAT関数に置き換える必要があります。
なお、MySQLにおけるFORMAT関数は、数字をコンマ区切りで表示するための関数です。そのため、SEL ServerからMySQLにSQLを移植した場合、FORMAT関数をLPAD関数に置き換えし忘れると、FORMAT関数の引数が間違っている、といったメッセージが表示されるので注意してください。
まとめ
これまで解説したように、ゼロ埋めの数字をSQLで出力するには、LADP関数を使います。ただし、マイクロソフト製のSQL Serverなどでは、FOAMAT関数を使います。なお、LADP関数を使った場合でも、第1引数が文字列でない場合は、PostgreSQLで正しく動作しないなどの違いがあるので、SQLを移植する際は注意が必要です。
