受講料が最大70%OFF 受講料が最大70%OFF

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

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

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

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

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 ゼロパディングのサンプルコード集 | 「ポテパンスタイル」

まとめ

ポテパンダの一言メモ
  • SQLで数値から文字列に変換する場合は、変換処理は不要
  • ただし、高精度の数値として扱いたい場合や、ゼロ詰めなどフォーマットしたい場合は、to_numberやto_charなどで変換する
  • MySQLなど、to_charが用意されていないデーターベースでは、lpadを使って数値のフォーマットをおこなう

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。