スカラー関数 

スカラー関数は、表 4.2 のカテゴリに分類されます。

 
スカラー関数のカテゴリ
 
関数のカテゴリ説明
組み込みデータベースに組み込まれている値または設定に対する操作を実行します。Oracle の場合、"組み込み" という用語は、Oracle で提供されている (DBMS に組み込まれている) 特殊な関数全般のことを指します。Oracle における "組み込み" は、ここで説明している組み込み関数とはまったく別のものです。
日時日時フィールドに対する操作を実行したり、日時フォーマットで値を返したりします。
数値数値に対する操作を実行したり、数値を返したりします。
文字列文字値 (char、varchar、nchar、nvarchar、および CLOB) に対する操作を実行したり、文字列または数値を返したりします。
 
CASE と CAST も関数ですが、これらについては第 3 章で説明しています。これらの関数は複雑で、SQL データステートメントで使用されることが多いためです。
組み込みスカラー関数

SQL99 の組み込みスカラー関数では、現在のユーザーセッションや、その属性 (現在のセッション権限など) を識別できます。組み込みスカラー関数は、ほぼ常に非決定性です。表 4.3 の最初の 3 つの関数は、 日時関数に分類される組み込み関数です。先に述べた 4 つのベンダーでは、これらの SQL 組み込み関数以外にも多くの関数を提供していますが、SQL 標準で定義されているのは、表 4.3 に示した関数のみです。

 
SQL99 の組み込みスカラー関数
 
関数目的
CURRENT_DATE現在の日付を識別します。
CURRENT_TIME現在の時刻を識別します。
CURRENT_TIMESTAMP現在の日時を識別します。
CURRENT_USERデータベースサーバーで現在アクティブなユーザーを識別します。
SESSION_USER 現在アクティブな認証 ID を識別します (ユーザーと異なる場合)。
SYSTEM_USER ホストオペレーティングシステムで現在アクティブなユーザーを識別します。
 
Microsoft SQL Server では、すべての組み込みスカラー関数がサポートされています。Oracle では、上記の組み込みスカラー関数はサポートされていません。ただし、CURRENT_USER のシノニムとして USER と、CURRENT_TIMESTAMP のシノニムとして SYSDATE がサポートされています。MySQL では、すべての SQL99 組み込みスカラー関数と、前述の Oracle の 2 つの関数がサポートされています。PostgreSQL では、CURRENT_USER のシノニムとして、SQL99 で定義されている USER がサポートされています。さらに MySQL では、CURRENT_TIMESTAMP のシノニムとして、NOW( ) と UNIX_TIMESTAMP( ) がサポートされています。PostgreSQL では、SESSION_USER を除くすべての SQL99 組み込みスカラー関数がサポートされています。
 
 

次のクエリーは、組み込み関数から値を取得します。日付は、各データベースのネイティブフォーマットで返されます。

 
/* On MySQL */
SELECT CURRENT_TIMESTAMP;
-> '2001-12-15 23:50:26'

/* On Microsoft SQL Server */
SELECT CURRENT_TIMESTAMP
GO
-> 'Dec 15,2001 23:50:26'

/* On Oracle */
SELECT USER FROM dual;
-> dylan
数値スカラー関数

SQL99 で正式に定義されている数値関数は、それほど多くありません。これを補うために、さまざまなベンダーで非常に多くの数学関数および統計関数が提供されています。MySQL は、SQL99 の実装において、これらのコマンドの多くをサポートしています。その他のデータベース製品は、数値スカラー関数と同じ機能を独自の内部定義関数として提供していますが、これらの関数には SQL 標準とは異なる名前が付けられています。SQL99 でサポートされている数値関数とその構文を表 4.4 に示します。

 
SQL99 の数値関数
 
関数目的
BIT_LENGTH(expression)式に含まれるビット数を表す整数値を返します。
CHAR_LENGTH(expression)式に含まれる文字数を表す整数値を返します。
EXTRACT(datetime_expression datepart FROM expression)式から、datepart で指定された日付要素 (YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、TIMEZONE_HOUR、または TIMEZONE_MINUTE) を抽出します。
OCTET_LENGTH(expression)式に含まれるオクテット数を表す整数値を返します。この値は、BIT_LENGTH/8 と同じです。
POSITION(starting_string IN search_string)検索文字列中での、ある文字列の開始位置を表す整数値を返します。
 
 
BIT_LENGTH、CHAR_LENGTH、および OCTET_LENGTH

BIT_LENGTH 関数に最も近い関数を提供しているのは、Oracle です。Oracle では、式に含まれるバイト数を表す整数値を返す LENGTHB 関数がサポートされています。

MySQL と PostgreSQL では、CHAR_LENGTH と、SQL99 のシノニムである CHARACTER_LENGTH( ) がサポートされています。PostgreSQL ではこれらに加え、SQL99 に準拠して EXTRACT( )、OCTET_LENGTH( )、および POSITION( ) がサポートされています。Microsoft SQL Server と Oracle にも、同じ機能を提供する類似の関数があります。Microsoft SQL Server では LEN 関数、Oracle では LENGTH 関数が提供されています。

MySQL と PostgreSQL では、OCTET_LENGTH 関数も完全にサポートされています。

 

次の例では、文字列の長さと、列から取得される値の長さを調べます。

 
/* On MySQL and PostgreSQL */
SELECT CHAR_LENGTH('hello');
SELECT OCTET_LENGTH(book_title) FROM titles;

/* On Microsoft SQL Server */
SELECT DATALENGTH(title)
FROM titles
WHERE type = 'popular_comp'
GO

/* On Oracle */
SELECT LENGTH('HORATIO') "Length of characters"
FROM dual;
 
EXTRACT

EXTRACT 関数は、PostgreSQL と MySQL 以外のデータベースベンダーではサポートされていません。

Oracle と Microsoft SQL Server では、同じ機能を提供する別のコマンドがサポートされています。Oracle では、日付の一部を抽出して文字列に変換するのに、TO_CHAR 関数を使用します。Microsoft SQL Server では、日付の一部を抽出するのに CONVERT 関数を使用します。

MySQL の実装では、SQL99 標準の一部が拡張されています。SQL99 標準では、同じ EXTRACT( ) 呼び出しの中で複数のフィールド ("DAY_HOUR" など) を返すための方法は規定されていません。MySQL の拡張は、PostgreSQL における DATE_TRUNC( ) と DATE_PART( ) の組み合わせと同じことを実現しようとするものです。MySQL では、 表 4.5 に示す日付要素がサポートされています。

 
MySQL の日付要素
 

意味

期待されるフォーマット

SECOND

MINUTE

HOUR

DAY

MONTH

YEAR

MINUTE_SECOND

分と秒

"分:秒"

HOUR_MINUTE

時と分

"時:分"

DAY_HOUR

日と時

"日 時"

YEAR_MONTH

年と月

"年-月"

HOUR_SECOND

時、分、秒

"時:分:秒"

DAY_MINUTE

日、時、分

"日 時:分"

DAY_SECOND

日、時、分、秒

"日 時:分:秒"

 

次の例では、いくつかの日時値から日付要素を抽出します。

 
/* On MySQL  */
SELECT EXTRACT(YEAR FROM "2013-07-02");
-> 1999
SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03");
-> 199907
SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03");
-> 20102
 
POSITION

POSITION 関数は、検索文字列中での、ある文字列の開始位置を表す整数値を返します。MySQL と PostgreSQL では、SQL99 の構文とまったく同じ形で POSITION 関数がサポートされています。これと同義の関数として、PostgreSQL には TEXTPOS、MySQL には LOCATE があります。

Oracle では、INSTR という関数が POSITION に相当します。Microsoft SQL Server には、CHARINDEX と PATINDEX という関数があります。CHARINDEX と PATINDEX はよく似ていますが、PATINDEX では検索条件にワイルドカードを使用できる点が異なります。次に例を示します。

/* On MySQL */
SELECT LOCATE('bar', 'foobar');
-> 4

/* On MySQL and PostgreSQL */
SELECT POSITION('fu' IN 'snafhu');
-> 0

/* On Microsoft SQL Server */
SELECT CHARINDEX( 'de', 'abcdefg' )
GO
-> 4
SELECT PATINDEX( '%fg', 'abcdefg' )

GO
-> 6
文字列関数

基本的な文字列関数群には数多くの機能があります。文字列関数は、結果セットとして文字列値を返します。一部の文字列関数には項が 2 つあり、一度に 2 つの文字列を処理します。SQL99 でサポートされている文字列関数を表 4.6 に示します。

 
SQL99 の文字列関数
 

関数

目的

CONCATENATE(expression || expression)

2 つ以上のリテラル式、列値、または変数を 1 つの文字列として連結します。

CONVERT

文字列を、同じ文字セットの範囲内で別の表現に変換します。

LOWER

文字列をすべて小文字に変換します。

SUBSTRING

文字列の一部を抽出します。

TRANSLATE

文字列の文字セットを別の文字セットに変換します。

TRIM

文字列から、先頭の文字、末尾の文字、またはその両方を削除します。

UPPER

文字列をすべて大文字に変換します。

 
CONCATENATE

SQL99 には、2 つの異なる文字列を 1 つの文字列値に結合する 連結演算子 (||) が定義されています。CONCATENATE 関数は、2 つ以上の文字列を連結して 1 つの文字列を生成します。PostgreSQL と Oracle では、二重連結演算子がサポートされています。Microsoft SQL Server では、連結演算子として プラス記号 (+) を使用します。

MySQL では、同様の関数として CONCAT( ) がサポートされています。Oracle、PostgreSQL、および Microsoft SQL Server における連結の詳細については、第 3 章の「連結演算子」を参照してください。

 
SQL99 の構文
 
CONCATENATE('string1' || 'string2')
 
MySQL の構文
 
CONCAT(str1, str2, [,...n])

いずれかの連結対象値が NULL の場合は、返される文字列全体が NULL になります。また、数値を連結する場合は、暗黙的に文字列に変換されます。

 
SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...');
-> 'My bologna has a first name...'
SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...');
-> NULL
 
CONVERT と TRANSLATE

CONVERT 関数は、同じ文字セットおよび照合順序の範囲内で、文字列の表現を変換します。たとえば、文字ごとのビット数を変更するために CONVERT を使用できます。

TRANSLATE は、文字列値の基本文字セットを別の文字セットに変更します。たとえば、ある値を英語文字セットから漢字 (日本語) 文字セットやロシア語文字セットに変換するために TRANSLATE を使用できます。変換のロジックは、デフォルトで存在しているか、または CREATE TRANSLATION コマンドを使用して作成しておく必要があります。

 
SQL99 の構文
 
CONVERT (char_value target_char_set USING form_of_use source_char_name)

TRANSLATE(char_value target_char_set USING translation_name)

データベースベンダーのうち、SQL99 と同じ意味での CONVERT と TRANSLATE をサポートしているのは Oracle だけです。Oracle による TRANSLATE の実装は SQL99 にかなり近いものですが、まったく同一というわけではありません。Oracle の実装では、指定できる引数は 2 つのみで、データベース文字セットまたは NLS (National Language Support) 文字セットとの間の変換のみがサポートされています。

MySQL による CONVERT 関数の実装では、数字の進数を別の進数に変換することのみがサポートされています。これとは対照的に、Microsoft SQL Server による CONVERT の実装には、式の基本データタイプを変換するさまざまな機能が備わっていますが、それ以外の点では SQL99 の CONVERT 関数とは異なったものになっています。PostgreSQL では CONVERT はサポートされていません。また PostgreSQL の TRANSLATE は、文字列を別の文字列に置換するための関数です。

 
MySQL の構文およびバリエーション
 
CONV(int, from_base, to_base)

MySQL では、TRANSLATE はサポートされていません。MySQL による CONVERT の実装では、変換元進数 (from_base) から変換先進数 (to_base) に変換した数字を表す文字列値が返されます。いずれかの数字が NULL の場合、この関数は NULL を返します。次に例を示します。

 
SELECT CONV("a",16,2);
-> '1010'
SELECT CONV("6E",18,8);
-> '172'
SELECT CONV(-17,10,-18);
-> '-H'
 
Microsoft SQL Server の構文およびバリエーション
 
CONVERT (data_type[(length) | (precision,scale)], expression[,style])

Microsoft SQL Server では、TRANSLATE はサポートされていません。また、CONVERT 関数の実装は SQL99 の仕様には準拠していません。Microsoft SQL Server の CONVERT は、機能的には CAST 関数と同じです。style 句は、日付変換のフォーマットを定義するために使用します。詳細については、ベンダーのマニュアルを参照してください。次に例を示します。

 
SELECT title, CONVERT(char(7), ytd_sales)
FROM titles
ORDER BY title
GO
 
Oracle の構文およびバリエーション
 
CONVERT('char_value', target_char_set, source_char_set)

TRANSLATE('char_value', 'from_text', 'to_text')

Oracle による実装では、CONVERT 関数は char_value を変換先文字セットで返します。char_value は変換対象の文字列で、target_char_setchar_value の変換先の文字セットの名前です。source_char_set は、char_value の格納に使用されていた元の文字セットの名前です。

変換先文字セットと変換元文字セットには、リテラル文字列、変数、または文字セットの名前を格納した列のいずれかを指定できます。変換先または変換元の文字セットが、変換で使用されるすべての文字を表現できない場合、文字が不適切に置き換えられる可能性があります。

Oracle でサポートされている一般的な文字セットには、US7ASCIIWE8DECDECWE8HPF7DECWE8EBCDIC500WE8PC850WE8ISO8859P1 などがあります。次に例を示します。

 
SELECT CONVERT('Gro', 'US7ASCII', 'WE8HP')
FROM DUAL;
->Gross
 
PostgreSQL の構文およびバリエーション
 
TRANSLATE (character_string, from_text, to_text)

PostgreSQL では、CONVERT はサポートされていません。PostgreSQL による TRANSLATE 関数の実装は、SQL99 の仕様と比較して、機能を大幅に拡張したスーパーセットになっています。PostgreSQL の TRANSLATE 関数は、指定された文字列中に出現するあるテキスト文字列を別のテキスト文字列に変換します。次に例を示します。

 
SELECT TRANSLATE('12345abcde', '5a', 'XX');
-> 1234XXbcde

SELECT TRANSLATE(title, 'Computer', 'PC')
FROM  titles
WHERE type = 'Personal_computer'
 
LOWER と UPPER

LOWER 関数と UPPER 関数では、 文字列の大文字と小文字を簡単に変更することが可能で、すべての文字をそれぞれ小文字または大文字に変換できます。これらの関数は、本マニュアルで扱うすべてのデータベースでサポートされています。

 
 
SELECT LOWER('You Talkin To ME?'), UPPER('you talking to me?!');
-> you talking to me?, YOU TALKIN TO ME?!

他にも、さまざまなデータベースで、各ベンダー固有のさまざまなテキストフォーマット関数がサポートされています。

 
SUBSTRING

SUBSTRING 関数は、ある文字列から別の文字列を抽出します。

 
SQL99 の構文
 
SUBSTRING(extraction_string FROM starting_position [FOR length]
[COLLATE collation_name])

いずれかの入力が NULL の場合、SUBSTRING 関数は NULL を返します。extraction_string は、文字値の抽出元です。抽出元には、リテラル文字列、テーブル内の文字データタイプの列、または文字データタイプの変数を指定できます。starting_position は、抽出の開始位置を指定する整数値です。オプションの length は、starting_position から何文字分を抽出するかを指定する整数値です。

 
MySQL の構文およびバリエーション
 
SUBSTRING(extraction_string FROM starting_position)

MySQL の実装では、開始位置から文字列末尾までの文字が抽出されます。

 
Microsoft SQL Server の構文およびバリエーション
 
SUBSTRING(extraction_string [FROM starting_position] [FOR length])

Microsoft SQL Server は SQL99 標準をほぼサポートしていますが、COLLATE 句は使用できません。Microsoft SQL Server では、このコマンドをテキスト、イメージ、およびバイナリの各データタイプに対して使用できます。ただし、starting_position と length は、文字数ではなくバイト数を表します。

 
Oracle の構文およびバリエーション
 
SUBSTR(extraction_string, starting_position [, length])

Oracle の実装である SUBSTR は SQL99 標準とほぼ同様に動作しますが、COLLATE 句はサポートされていません。starting_position が負の数の場合は、extraction_string の末尾からカウントされます。length が省略されている場合、starting_position 以降の文字列がすべて返されます。

 
PostgreSQL の構文およびバリエーション
 
SUBSTRING(extraction_string [FROM starting_position] [FOR length])

PostgreSQL は SQL99 標準をほぼサポートしていますが、COLLATE 句は使用できません。

 

次の例は、基本的に、本マニュアルで扱っている 4 つのデータベースのいずれでも動作します (Oracle の実装名である SUBSTR を SUBSTRING に変更した場合)。ただし、開始位置が負である 2 番目の Oracle の例だけは、他のデータベースでは動作しません。

 
/* On Oracle, counting from the left */
SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;
-> CDEF

/* On Oracle, counting from the right */
SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL;
-> CDEF

/* On MySQL */
SELECT SUBSTRING('Be vewy, vewy quiet',5);
-> 'wy, vewy quiet''

/* On PostgreSQL or SQL Server */
SELECT au_lname, SUBSTRING(au_fname, 1, 1)
FROM authors
WHERE au_lname = 'Carson'
-> Carson     C
 
TRIM 

TRIM 関数は、指定された文字列から、先頭の空白、末尾の文字、またはその両方を削除します。また、指定された文字列からその他のタイプの文字を削除することもできます。デフォルトの動作は、指定された文字を文字列の両端から削除することです。削除する文字が指定されていない場合、デフォルトでは空白が削除されます。

 
SQL99 の構文
 
TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_string] FROM ]
  target_string
  [COLLATE collation_name])

removal_string は、削除対象の文字列です。target_string は、文字の削除元となる文字列です。removal_string を指定しない場合、空白が削除されます。COLLATE 句は、関数の結果セットに対し、強制的に別の既存の照合順序セットを適用します。

MySQL、PostgreSQL、および Oracle では、TRIM の SQL99 構文がサポートされています。

Microsoft SQL Server (とその他のベンダー) は、先頭の空白または末尾の空白を削除するために、それぞれ LTRIM 関数と RTRIM 関数を提供しています。LTRIM と RTRIM では、その他の種類の文字を削除することはできません。

 
 
SELECT TRIM('   wamalamadingdong  ');
-> 'wamalamadingdong'

SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN');
-> '76 AMC GREMLIN'

SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx');
-> 'WHISKEY'

SELECT TRIM(TRAILING 'snack' FROM 'scooby snack');
-> 'scooby '