Oracleで使用可能な関数一覧【SQL入門】

参考記事

oracleで使用可能な関数一覧

oracleでは以下の関数が使用可能である。

二種類の関数

SQLの関数には二種類ある

一つが単一行関数

もう一つがグループ関数

グループ関数は複数の結果を集計して出力するのが特徴

  • 例)MAX関数(複数の値から最大のものを返すもの)

対して単一行関数は複数の結果に対して一つの結果を出力するのが特徴

  • 例)UPPER関数(太文字に変換する)

UPPER関数

文字列を全て大文字に書き換えることができる関数


SELECT name, UPPER(name) FROM employees;

>satomi, SATOMI

上記の例では「satomi」というフィールドを「SATOMI」に変換している

LOWER関数

全ての文字列を小文字に変えることができる関数

INTCAP関数

  • 単語の先頭にあるアルファベットを大文字に、

  • それ以外を小文字に直す


select INTCAP("test code") from dual;

> Test Code

単語として区切られる文字列はスペースの他に-や,も認識される

CONCAT関数

文字列を結合する関数


select
    CONCAT("Hello", "World")
from
    dual;

> HelloWorld

注意!結合できる文字列は二つだけ!、三文字以上の結合はできない!

SUBSTR関数

範囲を選択して文字列を取り出す関数

SUBSTR(m,n)なら

m文字目からn文字だけ取り出すという意味

1スタートなのは注意!


select
    SUBSTR("test code", 6, 3)
from
    dual;

> cod

LENGTH関数

文字数を表す関数


select
    LENGTH("あいうえお")
from
    dual

> 5

INSTR関数

指定した文字列パターンが現れる位置 を表す関数

最後まで文字がない場合はINSTRは0を返す。


select 
    instr('Oracle server', 'er', 1, 3),
    instr('Oracle server','er')
FROM dual;

> 12, 9

LPAD,RPAD関数

n文字になるように埋め込みを行う関数


select
    yomi,
    LPAD(yomi, 10, '*'),
    RPAD(yomi, 10, '$'),
from
    employees;

>sato,
******sato
sato$$$$$$

上記の例だと「sato」が10文字になるように埋め合わせを行なっている。

LPADは左にパディングを(padding Left の略)

RPADは右にパディングを(padding right の略)

TRIM関数

TRIMの第一引数には三通りの選択があり

  • LEADINGなら「文字列の先頭にある削除文字を削除する」

  • TRALINGなら「文字列の末尾にある削除文字を削除する」

  • BOTH(デフォルトの値)なら「先頭と末尾両方の文字列を削除する」

という挙動に変わる

次の例では、Oracle Serverから,先頭のOが取り除かれている


SELECT TRIM(LEADING 'O' FROM 'Oracle Server')
FROM dual;

> racle Server

注意!TRIMの第一引数を省略するとBOTHが適用されます

REPLSCE関数

マッチした文字列を全て書き換える関数

書き換える文字列を省略した場合は、対象の文字列を削除する挙動に変わる


select
    REPLACE('Oracle Server', 'Server', 'Hello!!!'),
    REPLACE('Oracle Server', 'Server')
FROM dual;

> Oracle Hello!!!  Oracle

ROUND関数

小数点以下n桁を四捨五入する関数

桁数の指定を行わない場合は整数値になるように四捨五入される

負の値を返すことで、整数値の値も四捨五入できる


ROUND(12345.678, 1)
> 12345.7

ROUND(12345.678)
>12346

ROUND(12345.678, -1)
>12350

TRUNC関数

小数点以下n桁に切り捨てる関数

桁の繰り上げを行わないのが特徴。


ROUND(12345.678, 1)
> 12345.6

ROUND(12345.678)
>12345

MOD関数

割り算の余りを返す関数


MOD(10,3)
>1

MOD(20,4)
>0

現在時刻を返す関数

sysdateは現在の時刻を返す関数


select
    sysdate
from dual;

>2021-11-10 14:15:00

日付の四則演算

日付の計算における1は「一日」を表す

よって、一時間を表したい時は「1/24」と表記する


sysdate -1,
>> 2021-11-9 14:15:00

sysdate -1/24
>> 2021-11-10 13:15:00

日付-日付

日付同士の四則演算は「日数」が返される


2021-11-10 13:15:00 - 2021-11-9 13:15:00
> 1

日付 + 日付

エラーが出る

(日付と日付の四則演算はできません。)

MONTHS_BETWEEN関数

二つの日付の月数を戻す

次の関数は雇った日から何ヶ月たったかを表す(4ヶ月)


select
    MONTHS_BETWEEN(sysdate, hire_date)
from
    employees;

> 4

ADD_MONTHS関数

日付に対して加算、引き算ができる。


ADD_MONTHS(sysdate, -1)
> 2021-10-10 13:15:00

NEXT_DAY関数

指定された曜日の翌日以降に指定された曜日になる日付(つまり一週間後)を返す関数


NEXT_DAY(日付,'曜日')

曜日は「日曜日」「月曜日」 または「日」「月」...の形式

英語だと 「SUM」「MON」...の形式

LAST_DAY関数

日付に指定された月の最終日を持ってくる関数


LAST_DAY("2021-01-21")

> 2021-01-31

ROUND関数

指定された日付を四捨五入して表す関数


ROUND(日付, 'YEAR')
ROUND(日付, 'MOMNTH')
ROUND(日付, 'DD')

例)
ROUND("2021-01-21",'YEAR')

> 2021-02-01

TRUNC関数

指定された日付を四捨五入して表す関数


TRUNC(日付, 'YEAR')
TRUNC(日付, 'MOMNTH')
TRUNC(日付, 'DD')

例)
TRUNC("2021-01-21",'YEAR')

> 2021-01-01

title:SQLで使える関数一覧【SQL基礎入門】

description:oracleマスターbronzeのweb問題集です。間違いやすい問題に厳選してあります。

img:https://www.oreilly.co.jp/books/images/picture_large4-87311-281-8.jpeg

c