SQLで、指定した桁数を0で埋めるゼロパディング処理についてまとめています。
数値を指定桁数まで０で埋めるには、lpadを使う
例えば、数値の1を４桁の０埋め（０パディング）して0001にするには、lpad関数が便利です。
以下の書式で使用できます。
lpad(値,桁数,埋める文字)
MySQLで実際にSQLを実行してみましょう。
// 4桁の0埋め
mysql> select lpad(1, 4, '0');
+-----------------+
| lpad(1, 4, '0') |
+-----------------+
| 0001 |
+-----------------+
1 row in set (0.00 sec)
//2桁の数字を4桁の0埋め
mysql> select lpad(99, 4, '0');
+------------------+
| lpad(99, 4, '0') |
+------------------+
| 0099 |
+------------------+
1 row in set (0.00 sec)
//4桁の数字を4桁の0埋め （そのまま出力される）
mysql> select lpad(1234, 4, '0');
+--------------------+
| lpad(1234, 4, '0') |
+--------------------+
| 1234 |
+--------------------+
1 row in set (0.00 sec)
//1を文字列'A'で左詰め
mysql> select lpad(1, 4, 'A');
+-----------------+
| lpad(1, 4, 'A') |
+-----------------+
| AAA1 |
+-----------------+
1 row in set (0.00 sec)
//1を0で右詰めで埋める
mysql> select rpad(1, 4, '0');
+-----------------+
| rpad(1, 4, '0') |
+-----------------+
| 1000 |
+-----------------+
1 row in set (0.00 sec)
//文字xを左詰めでzで埋める
mysql> select lpad('x',4,'z');
+-----------------+
| lpad('x',4,'z') |
+-----------------+
| zzzx |
+-----------------+
1 row in set (0.09 sec)
//数値1を左詰めでabcabcabc...で埋める
mysql> select lpad(1, 10, 'abc');
+--------------------+
| lpad(1, 10, 'abc') |
+--------------------+
| abcabcabc1 |
+--------------------+
1 row in set (0.00 sec)
MySQLの場合、テーブルの属性にZEROFILLを指定すると挿入された値が0埋めされて格納されます。
mysql> CREATE TABLE `demo`( `id` int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `num` int(3) ZEROFILL NOT NULL); Query OK, 0 rows affected, 3 warnings (0.28 sec) mysql> INSERT INTO `demo`(`num`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `demo`(`num`) VALUES (20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `demo`(`num`) VALUES (999); Query OK, 1 row affected (0.01 sec) mysql> select * from demo; +----+-----+ | id | num | +----+-----+ | 1 | 001 | | 2 | 020 | | 3 | 999 | +----+-----+ 3 rows in set (0.00 sec)
自動的に数値が増えるincrement属性のカラムにたいしてもZEROFILL指定が可能です。以下は、increment属性のカラムidを持つテーブルにデータを挿入した例です。
mysql> CREATE TABLE `demo2`( `id` int(10) zerofill NOT NULL PRIMARY KEY AUTO_INCREMENT, `num` int(3) NOT NULL); mysql> INSERT INTO `demo2`(`num`) VALUES (1); Query OK, 1 row affected (0.09 sec) mysql> INSERT INTO `demo2`(`num`) VALUES (20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `demo2`(`num`) VALUES (999); Query OK, 1 row affected (0.00 sec) mysql> select * from demo2; +------------+-----+ | id | num | +------------+-----+ | 0000000001 | 1 | | 0000000002 | 20 | | 0000000003 | 999 | +------------+-----+ 3 rows in set (0.07 sec)
【関連記事】
▶MySQLで利用可能な整数型について解説！int(11)のカッコに設定された数値って何？
Oracleでの0埋めはlpad。 to_charでの0字詰めには半角空白が入る
Oracleでもlpad関数で0埋めが可能です。
select lpad(1, 5, '0') from dual; lpad(1, 5, '0') --------------- 00001
Oracleでto_charを使って０字詰めをおこなうと、先頭に半角空白が入ります。以下の例は、文字列TESTに「１」の０字詰め「０１」を結合しようとした例です。
select 'TEST'||to_char(1, '00') name from dual; NAME ------- TEST 01
空白部分は符号が入るための領域です。数値がマイナスの場合は「-」が入ります。文字列操作関数などを使って符号部分を削除することも可能ですが、代わりにlpadもしくはrpadを使いましょう。rpadは右詰めの０パディングをおこなう関数です。
select 'TEST'||lpad(1, 2,'00') name from dual; NAME ------- TEST01
PogureSQLもlpadで0埋めが可能。第一引数は文字列型にする必要あり
PosgreSQLでもlpad関数が使用できます。ただし、第一引数は文字列型にする必要があります。
SELECT lpad('1',3,'0');
| lpad |
|------|
| 001 |
第一引数に整数型を指定すると、以下のエラーが出力されます。
SELECT lpad(1,3,'0'); ERROR: function lpad(integer, integer, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8
SQL ServerはRIGHT関数やFORMAT関数で0埋めする
SQL Serverには、ほかのDBMSにあるようなlpadやrpadなどの専用の字詰関数がありません。以下のようにright関数またはleft関数（右詰めの場合）を使います。
SELECT RIGHT('00000' + CONVERT(NVARCHAR, 11), 5);
| |
|-------|
| 00011 |
また、日付やカンマ区切りに使うformat関数を使って０パディングが可能です。
SELECT FORMAT(1,'0000'); | | |------| | 0001 |
まとめ
ポテパンダの一言メモ
- MySQLなどではlpad関数で0埋めが可能
- Oracleでは、to_char関数を使って0埋めすると半角空白が入るので、lpadを使う
- PostgreSQLでは、lpad関数の第一引数を文字列型にする必要あり
- SQL Serverでlpad関数は使えない。right関数やformat関数を使って0埋めする。
