データベース千夜一夜第24回

SQLと関数~変換関数、IS関数、その他の関数と命令 長谷川裕行
有限会社 手國堂

変換関数

データ型を変換する関数です。SQL Serverでは式の形によっては自動的に適切な型へ自動的に変換されますが、数値や日付を指定したサイズ・書式で文字列として扱いたい場合などには、明示的な型変換が必要になります。


- 文字列型へ変換~STR -

機能:数値型のデータを文字列型に変換します。

書式:STR(<数値>, <長さ>, <桁数>)

引数
 <数値>:float型の数値。関数やサブクエリは使えません。
 <長さ>:結果として返される文字数。変換結果の小数点、符号、数字を含みます。既定値は10で省略可能です。
 <桁数>:結果として返される小数部の桁数

戻り値:char型

例)テーブル「商品_mr」から、各商品の利益率を計算し、文章で表示します。

利益率(%)は
  (販売単価-仕入単価)÷販売単価×100
という式で求めます。

  SELECT 商品ID,
  品名 + 'の利益率は ' +
  STR((販売単価-仕入単価)/販売単価*100,6,2) + ' %です。'
  FROM 商品_MR

STR関数を使うと、文字列連結演算子(+)を使って数値データを文字列中に埋め込むなど、様々な加工が行えます。



- データ型変換~CAST、CONVERT -

データ型を変換する関数には、CASTとCONVERTの2つがあります。CAST関数は単純な型変換を行いますが、CONVERT関数は書式を指定しての型変換が行えます。

・CAST

機能:値のデータ型を変換します。

書式:CAST (<値> AS <型>)

引数
 <値>:変換対象の値(式)
 <型>:変換後の型

戻り値:引数<型>で示す型の値

例)テーブル「商品_mr」から、各商品の利益率を計算して6桁の文字列として表示します。

  SELECT 商品ID, 品名,
  CAST((販売単価-仕入単価)/販売単価*100 AS char(6)) AS '利益率(%)'
  FROM 商品_mr


・CONVERT

機能:値のデータ型を変換します。主に日付/時刻型の値の書式設定に用います。

書式:CONVERT (<型>, <値>, <書式番号>)

引数
 <型>:変換後の型
 <値>:変換対象の値(式)
 <書式番号>:小数を含む数値や日付/時刻型の値を文字列に変換する際の書式を示す番号

戻り値:引数<型>で示す型の値

例)テーブル「得意先_mr」の「生年月日」を“yyyy/mm/dd”形式で表示します。

  SELECT 氏名,
  CONVERT(varchar, 生年月日, 111) AS 生年月日
  FROM 得意先_mr



 <書式番号>は、日付/時刻型の値や小数を含む値を文字列型に変換する際に指定します。特に日付/時刻型では非常に多彩な変換ができますが、主なものに絞って紹介しておきましょう。

(「例」はfloat型の値10.0000を3で割った場合の変換結果です)

表1の日付/時刻型の変換では、★マークを付けたISO形式への変換が、文字列連結を利用して年月日を含む伝票番号を生成する際に便利です。

なお、CASTやCONVERT関数を使わなくても、通貨型からintやfloatなどの数値型への変換や、日付/時刻型から文字列型(規定の日付/時刻表示形式)への変換は自動的に行われます。

通貨型を文字列型に変換するような場合や、日付/時刻型を規定形式以外の形式に変換する場合にはCONVERT関数を使います。


- 値のバイト数を得る~DATALENGTH -

機能:値を表示する際に必要なバイト数を返します。

書式:DATALENGTH (<値>)

引数:バイト数を求める値

戻り値:int型

例)テーブル「得意先_mr」の「住所1」と「住所2」に必要なバイト数を表示します。

  SELECT 氏名,
  住所1, DATALENGTH(住所1) AS バイト数1,
  住所2, DATALENGTH(住所2) AS バイト数2
  FROM 得意先_mr



前回簡単に紹介しましたが、使い方によっては意外と応用の利く関数なのでもう少し詳しく説明しておきます。

DATALENGTH関数は、文字列や小数、指数形式の値、表示形式を指定した日付/時刻型などをアプリケーションのフォームに表示される際、テキストボックスやウィンドウのサイズをデータの長さに合わせて動的に変更するような場合に役立ちます。

また、可変長の型(varchar、varbinary、textなど)のフィールドに実際に何バイト格納されているかを調べる場合にも便利です。

なお、画面4にあるように、NULLとなっているフィールドではDATALENGTH関数の戻り値もNULLとなります。0ではないので注意が必要です。


トップページ
変換関数
文字列型へ変換~STR
データ型変換~CAST、CONVERT
値のバイト数を得る~DATALENGTH
IS関数
その他の関数と命令
あとがき
Copyright © GrapeCity inc. All rights reserved.