SQLで文字列と数値を変換する数値をゼロ詰めフォーマットする方法

SQLの文字列の数値変換についてまとめています。

MySQLのサンプルデータベースEmployeesを使用しています。

1 SQLの文字列を数値に変換する方法と、ゼロ詰めフォーマット
2 まとめ

SQLの文字列を数値に変換する方法と、ゼロ詰めフォーマット

主要なデータベース（Oracle、MySQL、SQL Server、PostgreSQL）では、手動で文字列を数値に変換する必要はありません。

たとえば、数字の1と文字列の’1’を足し算すると、自動的に型変換が行われます。※以下はMySQLでの実行例です。

mysql> select 1+'1';
+-------+
| 1+'1' |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Oracleでも、同様に自動的に型変換が行われます。

※Oracleでの実行例
select 1+'1' from dual;

1+'1'
-----
2

文字列を関数の引数に指定する場合でも、変換は不要です。employees（社員）テーブルのemp_noは、int型で、emp_no-10000もint型になりますが、文字列を連結する関数concatの引数にそのまま使用してもエラーにはなりません。

mysql> select concat("No.",emp_no-10000), first_name, last_name  from employees limit 10;
+----------------------------+------------+-----------+
| concat("No.",emp_no-10000) | first_name | last_name |
+----------------------------+------------+-----------+
| No.1                       | Georgi    | Facello   |
| No.2                       | Bezalel    | Simmel    |
| No.3                       | Parto      | Bamford   |
| No.4                       | Chirstian  | Koblick   |
| No.5                       | Kyoichi    | Maliniak  |
| No.6                       | Anneke     | Preusig   |
| No.7                       | Tzvetan    | Zielinski |
| No.8                       | Saniya     | Kalloufi  |
| No.9                       | Sumant     | Peac      |
| No.10                      | Duangkaew  | Piveteau  |
+----------------------------+------------+-----------+
10 rows in set (0.00 sec)

例外として、桁数の多い高精度数値で計算を行いたい場合など、明示的にTO_NUMBER（Oracleの場合）などの文字列→数値変換関数で、変換をおこなうケースもあります。

select TO_NUMBER('10.5') from dual;

TO_NUMBER('10.5')
-----
10.5

数値を文字列に変換する場合、フォーマット文字列を指定することでゼロ詰めフォーマットをおこなうことができます。以下はPostgreSQｌでの例です。

123を0999というフォーマット文字列でフォーマットすると0123となります。0または9で数値をそのまま表示します。ただし、0が指定されている桁がゼロだった場合、0を表示します。

postgres=# select to_char(123, '0999');
 to_char 
---------
  0123
(1 row)

フォーマット文字に0を指定した場合と、9を指定した場合の具体的な例を見てみましょう。

postgres=# select to_char(100,'999');
 to_char 
---------
  100
(1 row)

postgres=# select to_char(100,'000');
 to_char 
---------
  100
(1 row)

postgres=# select to_char(0.1,'999.9');
 to_char 
---------
     .1
(1 row)

postgres=# select to_char(0.1,'990.9');
 to_char 
---------
    0.1
(1 row)

ゼロ詰めのほか、小数表示の整数部がゼロだった場合にゼロを表示したい場合にも、フォーマット文字0が使われます。

以下は1、100、1000にフォーマット文字000を指定した例です。桁が足りない場合は0詰めされます。桁があふれた場合、結果は###と、最大桁数ぶんの#が並べられます。

postgres=# select to_char(1, '000');
 to_char 
---------
  001
(1 row)

postgres=# select to_char(100, '000');
 to_char 
---------
  100
(1 row)

postgres=# select to_char(1000, '000');
 to_char 
---------
  ###
(1 row)

なお、to_charでは正数が指定された場合、符号を表示するための桁が空白となります。空白を入れたくない場合は、フォーマット文字にFMを指定します。

postgres=# select concat('#',to_char(-100, '000'),'#');
 concat 
--------
 #-100#
(1 row)

postgres=# select concat('#',to_char(100, '000'),'#');
 concat 
--------
 # 100#
(1 row)

postgres=# select concat('#',to_char(100, 'FM000'),'#');
 concat 
--------
 #100#
(1 row)

MySQLには、to_charという関数がありません。そのため、ゼロ詰めしたい場合は、lpadという関数を使います。

mysql> select lpad(1, 4, '0');
+-----------------+
| lpad(1, 4, '0') |
+-----------------+
| 0001            |
+-----------------+
1 row in set (0.00 sec)

SQLでの0埋めにはlpad ゼロパディングのサンプルコード集 | 「ポテパンスタイル」