SQL には、値を加工するための 関数 が用意されています。文字をつなげる、文字の一部を取り出す、数を丸める、日付を好きな形式の文字にする、といった処理を、SELECT の中で行えます。この章では、Oracle の関数のうち、現場でよく使うものに絞って学びます。
この章で学ぶこと
- 関数の書き方と、
DUAL表 - 文字列の関数：
||（連結）・LENGTH・SUBSTR・REPLACE・UPPER・LOWER - 数値の関数：
ROUND・TRUNC・MOD - 日付の関数：
SYSDATE・TO_CHAR・TO_DATE・MONTHS_BETWEEN - WHERE 句の中で関数を使う
8.1 関数とは
関数 は、値を受け取って、加工した値を返す部品です。関数名(値) の形で書き、( ) の中に渡す値を 引数 といいます。引数が複数あるときは , で区切ります。
関数名(引数1, 引数2, ...)
たとえば LENGTH(NAME) は、「NAME の値を受け取って、その文字数を返す」関数です。関数は SELECT の列の位置にも、WHERE の条件の中にも書けます。第5章の計算（AGE + 1）と同じく、関数は 結果の表示を加工するだけ で、テーブルの値は変わりません。
8.2 DUAL 表
関数の動きを試すとき、テーブルのデータは必要ありません。しかし SELECT 文には FROM が必須です。そこで Oracle には、1行1列だけの表 DUAL が最初から用意されています。第2章の SELECT USER FROM DUAL でも使いました。
SELECT 1 + 2 FROM DUAL;
| 1+2 |
|---|
| 3 |
DUAL は1行なので、結果も1行になります。この章では、テーブルの列を使わない例は DUAL で試します。
8.3 文字列の関数
|| ：文字列をつなげる
||（縦棒2つ）は、左右の文字列をつなげます。関数ではなく演算子ですが、文字列の加工で最もよく使うので、ここで学びます。氏名の後ろに「さん」を付けてみましょう。
SELECT NAME, NAME || 'さん' AS GREETING FROM EMPLOYEE ORDER BY EMP_ID;
| NAME | GREETING |
|---|---|
| 雷禅 海皇 | 雷禅 海皇さん |
| 院照 心愛 | 院照 心愛さん |
| 林檎 星飛 | 林檎 星飛さん |
| 田中 美咲 | 田中 美咲さん |
| 佐藤 健太 | 佐藤 健太さん |
| 鈴木 花子 | 鈴木 花子さん |
| 高橋 翔 | 高橋 翔さん |
| 伊藤 さくら | 伊藤 さくらさん |
| 渡辺 大輔 | 渡辺 大輔さん |
| 中村 結衣 | 中村 結衣さん |
いくつでもつなげられます。氏名と住所を1つの列にします。
SELECT NAME || '（' || ADDRESS || '）' AS PROFILE FROM EMPLOYEE ORDER BY EMP_ID;
| PROFILE |
|---|
| 雷禅 海皇（大阪府） |
| 院照 心愛（京都府） |
| 林檎 星飛（東京都） |
| 田中 美咲（愛知県） |
| 佐藤 健太（東京都） |
| 鈴木 花子（大阪府） |
| 高橋 翔（東京都） |
| 伊藤 さくら（兵庫県） |
| 渡辺 大輔（神奈川県） |
| 中村 結衣（京都府） |
数の列もつなげられます。数は自動で文字に変換されます。
SELECT NAME, AGE || '歳' AS AGE_LABEL FROM EMPLOYEE ORDER BY EMP_ID;
| NAME | AGE_LABEL |
|---|---|
| 雷禅 海皇 | 26歳 |
| 院照 心愛 | 27歳 |
| 林檎 星飛 | 24歳 |
| 田中 美咲 | 24歳 |
| 佐藤 健太 | 31歳 |
| 鈴木 花子 | 29歳 |
| 高橋 翔 | 35歳 |
| 伊藤 さくら | 22歳 |
| 渡辺 大輔 | 42歳 |
| 中村 結衣 | 26歳 |
【ご注意】+ では文字列をつなげられない
プログラミング言語では + で文字列をつなげるものが多いですが、Oracle の SQL で NAME + 'さん' と書くと、数の足し算として扱われ「ORA-01722: 数値が無効です。」というエラーになります。文字列をつなげるのは || です。
LENGTH・SUBSTR・REPLACE
| 関数 | 意味 | 例 | 結果 |
|---|---|---|---|
LENGTH(文字列) |
文字数を返す | LENGTH('雷禅 海皇') |
5（空白も1文字） |
SUBSTR(文字列, 開始位置, 文字数) |
開始位置（1 から数える）から、指定した文字数を取り出す | SUBSTR('雷禅 海皇', 1, 2) |
雷禅 |
REPLACE(文字列, 探す文字, 置き換える文字) |
探す文字をすべて置き換える | REPLACE('雷禅 海皇', ' ', '') |
雷禅海皇 |
氏名の文字数と、先頭2文字（姓）を表示します。
SELECT NAME, LENGTH(NAME) AS LEN, SUBSTR(NAME, 1, 2) AS FAMILY_NAME FROM EMPLOYEE ORDER BY EMP_ID;
| NAME | LEN | FAMILY_NAME |
|---|---|---|
| 雷禅 海皇 | 5 | 雷禅 |
| 院照 心愛 | 5 | 院照 |
| 林檎 星飛 | 5 | 林檎 |
| 田中 美咲 | 5 | 田中 |
| 佐藤 健太 | 5 | 佐藤 |
| 鈴木 花子 | 5 | 鈴木 |
| 高橋 翔 | 4 | 高橋 |
| 伊藤 さくら | 6 | 伊藤 |
| 渡辺 大輔 | 5 | 渡辺 |
| 中村 結衣 | 5 | 中村 |
この教材のサンプルデータは、姓が2文字なので SUBSTR(NAME, 1, 2) で姓を取り出せています。実際の業務では姓の文字数は決まっていないので、姓と名は最初から別の列に分けて持つのが一般的です。
氏名の姓と名の間の空白を取り除きます。
SELECT NAME, REPLACE(NAME, ' ', '') AS NO_SPACE FROM EMPLOYEE ORDER BY EMP_ID;
| NAME | NO_SPACE |
|---|---|
| 雷禅 海皇 | 雷禅海皇 |
| 院照 心愛 | 院照心愛 |
| 林檎 星飛 | 林檎星飛 |
| 田中 美咲 | 田中美咲 |
| 佐藤 健太 | 佐藤健太 |
| 鈴木 花子 | 鈴木花子 |
| 高橋 翔 | 高橋翔 |
| 伊藤 さくら | 伊藤さくら |
| 渡辺 大輔 | 渡辺大輔 |
| 中村 結衣 | 中村結衣 |
''（' を2つ続けたもの）は、空の文字列を表します。Oracle では、空の文字列は NULL と同じ扱いになりますが、REPLACE の3つ目の引数に書くと「探した文字を消す」という動きになります。
UPPER・LOWER
UPPER は英字を大文字に、LOWER は小文字にします。日本語には影響しません。この教材のデータには英字が無いので、DUAL で試します。
SELECT UPPER('oracle') AS UP, LOWER('ORACLE') AS LOW FROM DUAL;
| UP | LOW |
|---|---|
| ORACLE | oracle |
英字を含む列を検索するとき、WHERE UPPER(EMAIL) = UPPER('Taro@Example.com') のように両側を大文字にそろえると、大文字と小文字の違いを無視して比べられます。
8.4 数値の関数
| 関数 | 意味 | 例 | 結果 |
|---|---|---|---|
ROUND(数, 桁) |
小数点以下 桁 で四捨五入する。桁を省略すると整数にする |
ROUND(123.456, 1)ROUND(123.456)
|
123.5 123 |
TRUNC(数, 桁) |
小数点以下 桁 で切り捨てる。桁を省略すると整数にする | TRUNC(123.456, 1) |
123.4 |
MOD(数1, 数2) |
数1 を 数2 で割った余りを返す | MOD(10, 3) |
1 |
SELECT ROUND(123.456, 1) AS R1, ROUND(123.456) AS R0, TRUNC(123.456, 1) AS T1, MOD(10, 3) AS M FROM DUAL;
| R1 | R0 | T1 | M |
|---|---|---|---|
| 123.5 | 123 | 123.4 | 1 |
ROUND は、第9章で平均（AVG）を計算するときに、小数の桁をそろえるために使います。
8.5 日付の関数
SYSDATE：現在の日時
SYSDATE は、データベースの現在の日時を返します。( ) は付けません。
SELECT SYSDATE FROM DUAL;
| SYSDATE |
|---|
| 2026-10-20 21:07:03 |
結果は、実行した日時になります（上の表は例です）。DATE 型が時刻も持っていることが、ここで確かめられます。日付には数を足し引きでき、1 が1日 を表します。
SELECT SYSDATE + 7 AS NEXT_WEEK FROM DUAL;
| NEXT_WEEK |
|---|
| 2026-10-27 21:07:03 |
TO_CHAR：日付を文字にする
TO_CHAR(日付, '書式') は、日付を指定した書式の文字列にします。書式には、「開発環境の準備」の日付書式の設定で使ったものと同じ記号を使います。
| 記号 | 意味 | 例（2000年6月22日） |
|---|---|---|
YYYY |
4桁の年 | 2000 |
MM |
2桁の月 | 06 |
DD |
2桁の日 | 22 |
HH24・MI・SS
|
24時間表記の時・分・秒 | 00・00・00 |
"文字" |
" で囲んだ文字をそのまま入れる |
'YYYY"年"MM"月"DD"日"' → 2000年06月22日 |
SELECT NAME, TO_CHAR(BIRTH_DATE, 'YYYY/MM/DD') AS BIRTH FROM EMPLOYEE ORDER BY EMP_ID;
| NAME | BIRTH |
|---|---|
| 雷禅 海皇 | 2000/06/22 |
| 院照 心愛 | 1999/07/11 |
| 林檎 星飛 | 2001/12/24 |
| 田中 美咲 | 2002/03/05 |
| 佐藤 健太 | 1995/04/12 |
| 鈴木 花子 | 1997/01/30 |
| 高橋 翔 | 1991/08/15 |
| 伊藤 さくら | 2004/05/20 |
| 渡辺 大輔 | 1984/02/10 |
| 中村 結衣 | 2000/09/03 |
SELECT NAME, TO_CHAR(BIRTH_DATE, 'YYYY"年"MM"月"DD"日"') AS BIRTH FROM EMPLOYEE ORDER BY EMP_ID;
| NAME | BIRTH |
|---|---|
| 雷禅 海皇 | 2000年06月22日 |
| 院照 心愛 | 1999年07月11日 |
| 林檎 星飛 | 2001年12月24日 |
| 田中 美咲 | 2002年03月05日 |
| 佐藤 健太 | 1995年04月12日 |
| 鈴木 花子 | 1997年01月30日 |
| 高橋 翔 | 1991年08月15日 |
| 伊藤 さくら | 2004年05月20日 |
| 渡辺 大輔 | 1984年02月10日 |
| 中村 結衣 | 2000年09月03日 |
書式の一部だけを使えば、年だけ、月だけを取り出せます。TO_CHAR(BIRTH_DATE, 'YYYY') は '2000' のような4文字の文字列を返します。
【知識メモ】TO_CHAR の結果は文字列
TO_CHAR の結果は日付ではなく 文字列 です。表示を整えるのには便利ですが、日付として比べたり計算したりするときは、元の日付の列（BIRTH_DATE）を使います。Spring入門では、日付の表示の整形はアプリの画面側で行うので、SQL で TO_CHAR を使う場面は多くありません。
TO_DATE：文字を日付にする
TO_DATE('文字列', '書式') は、TO_CHAR の逆で、文字列を日付にします。'2000/06/22' のように / で区切った文字列を日付として使いたいときに使います。
SELECT NAME, BIRTH_DATE FROM EMPLOYEE WHERE BIRTH_DATE = TO_DATE('2000/06/22', 'YYYY/MM/DD');
| NAME | BIRTH_DATE |
|---|---|
| 雷禅 海皇 | 2000-06-22 00:00:00 |
この教材では、日付の値は DATE '2000-06-22' の書き方で統一しています。TO_DATE は、入力された文字列の形が決まっているとき（画面から 2000/06/22 の形で受け取るなど）に使います。書式を省略した TO_DATE('2000-06-22') や、文字列をそのまま日付の列と比べる書き方（BIRTH_DATE = '2000/06/22'）は、環境の設定によって動いたり動かなかったりするので、書式を必ず書く か DATE '…' を使ってください。
MONTHS_BETWEEN：日付の差
MONTHS_BETWEEN(日付1, 日付2) は、2つの日付の間の月数を返します。12 で割れば年数になります。生年月日から今日時点の年齢を計算し、AGE の列と比べてみましょう。
SELECT NAME, BIRTH_DATE,
TRUNC(MONTHS_BETWEEN(SYSDATE, BIRTH_DATE) / 12) AS CALC_AGE,
AGE
FROM EMPLOYEE
ORDER BY EMP_ID;
| NAME | BIRTH_DATE | CALC_AGE | AGE |
|---|---|---|---|
| 雷禅 海皇 | 2000-06-22 00:00:00 | 26 | 26 |
| 院照 心愛 | 1999-07-11 00:00:00 | 27 | 27 |
| 林檎 星飛 | 2001-12-24 00:00:00 | 24 | 24 |
| 田中 美咲 | 2002-03-05 00:00:00 | 24 | 24 |
| 佐藤 健太 | 1995-04-12 00:00:00 | 31 | 31 |
| 鈴木 花子 | 1997-01-30 00:00:00 | 29 | 29 |
| 高橋 翔 | 1991-08-15 00:00:00 | 35 | 35 |
| 伊藤 さくら | 2004-05-20 00:00:00 | 22 | 22 |
| 渡辺 大輔 | 1984-02-10 00:00:00 | 42 | 42 |
| 中村 結衣 | 2000-09-03 00:00:00 | 26 | 26 |
上の表は 2026年10月 に実行した結果です。この教材の AGE の値はこの時点の年齢なので、CALC_AGE と一致しています。誕生日を過ぎた人がいると、CALC_AGE の方が1つ大きくなります。TRUNC で小数を切り捨てているのは、「26.3年」を「26歳」にするためです。関数の中に関数を書く（入れ子にする）こともできます。
8.6 WHERE 句で関数を使う
関数は WHERE の条件の中にも書けます。氏名が6文字以上の社員を取り出します。
SELECT NAME, LENGTH(NAME) AS LEN FROM EMPLOYEE WHERE LENGTH(NAME) >= 6;
| NAME | LEN |
|---|---|
| 伊藤 さくら | 6 |
6月生まれの社員を取り出します。TO_CHAR で月だけを文字列にして比べます。
SELECT NAME, TO_CHAR(BIRTH_DATE, 'MM') AS BIRTH_MONTH FROM EMPLOYEE WHERE TO_CHAR(BIRTH_DATE, 'MM') = '06';
| NAME | BIRTH_MONTH |
|---|---|
| 雷禅 海皇 | 06 |
TO_CHAR の結果は文字列なので、比べる値も '06' と文字列で書きます。= 6 と数で書くと、Oracle が '06' を数に変換して比べるので動きますが、結果が分かりにくくなるので、型をそろえて書きます。
【ご注意】WHERE 句の別名は使えない
SELECT LENGTH(NAME) AS LEN … WHERE LEN >= 6 のように、SELECT で付けた別名を WHERE で使うことはできません（「ORA-00904: "LEN": 無効な識別子です。」）。WHERE は SELECT より先に処理されるためです（第6章）。WHERE には、関数や式をもう一度書きます。
8.7 うまくいかないときは
| 症状 | 考えられる原因と対処 |
|---|---|
| 「ORA-01722: 数値が無効です。」 | 文字列を + でつなげようとしているか、数に変換できない文字列を数と比べています。文字列の連結は || です。 |
| 「ORA-00904: "〇〇": 無効な識別子です。」 | 関数名の打ち間違い（LENGHT など）か、WHERE 句で別名を使っています。 |
| 「ORA-00909: 引数の個数が無効です。」 「ORA-00938: 関数の引数が不足しています。」 |
関数に渡す引数の数が違います。SUBSTR は2つか3つ、REPLACE は2つか3つ、MOD は2つです。 |
| 「ORA-01858: 数値を指定する箇所に数値以外の文字が指定されています」 | 日付として解釈できない文字列を、日付の列と比べているか TO_DATE に渡しています。DATE '2000-06-22' の書き方にするか、TO_DATE に文字列と合った書式を書いてください。 |
日付が 00-06-22 のように2桁の年で表示される |
「開発環境の準備」の日付書式の設定が反映されていません。設定を確認し、SQL Developer をいったん終了して、もう一度起動してから接続してください。 |
ほかのエラーは、付録B「よくあるエラーと対処法」 も参考にしてください。
8.8 まとめと確認問題
まとめ
- 関数は
関数名(引数, …)の形で書き、値を加工して返す。テーブルの値は変わらない。テーブルを使わずに試すときはDUAL表を使う - 文字列は
||でつなげる。LENGTH（文字数）・SUBSTR（一部を取り出す）・REPLACE（置き換え）・UPPER・LOWER - 数値は
ROUND（四捨五入）・TRUNC（切り捨て）・MOD（余り） -
SYSDATEは現在の日時。日付に数を足すと日数が進む。TO_CHAR(日付, '書式')で文字列に、TO_DATE('文字列', '書式')で日付にする。MONTHS_BETWEENで月数の差 - 関数は WHERE 句でも使える。SELECT の別名は WHERE では使えない
この章で使った関数は、付録C「SQL 早見表」の「関数」 にまとめています。
確認問題
問1 氏名と住所を「雷禅 海皇さんは大阪府に住んでいます」の形で表示する SELECT 文を書いてください。
解答を見る
SELECT NAME || 'さんは' || ADDRESS || 'に住んでいます' AS MSG FROM EMPLOYEE;
問2 SUBSTR('伊藤 さくら', 4, 3) の結果は何ですか。
解答を見る
さくら です。4文字目（「さ」）から3文字を取り出します。空白も1文字と数えます。
問3 ROUND(28.65, 1) と TRUNC(28.65, 1) の結果をそれぞれ答えてください。
解答を見る
ROUND(28.65, 1) は 28.7（四捨五入）、TRUNC(28.65, 1) は 28.6（切り捨て）です。
問4 生年月日を「1999年7月」のように年と月だけで表示したいとき、TO_CHAR の書式はどう書きますか（月は2桁でよい）。
解答を見る
TO_CHAR(BIRTH_DATE, 'YYYY"年"MM"月"') です。結果は 1999年07月 になります。
問5 SELECT NAME, LENGTH(NAME) AS LEN FROM EMPLOYEE WHERE LEN = 4; がエラーになる理由と、直し方を答えてください。
解答を見る
SELECT で付けた別名 LEN は、SELECT より先に処理される WHERE では使えないためです。WHERE LENGTH(NAME) = 4 と関数を書き直します。結果は 高橋 翔 の1行です。