【SQL】INSTR(インストリング)関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
INSTR関数の使用方法を紹介します。

INSTR関数の使い方
- INSTR関数の書式
1.INSTR関数引数をすべて指定した場合の使用例
2.INSTR関数「全角半角」「大文字小文字」「開始位置が負の値」の使用例

INSTR関数の使い方

INSTR関数は引数で指定した文字データ中に検索文字がある場合、その検索文字が文字データの先頭から何文字目にあるかを数値で返す関数です。
検索文字が存在しない場合は「0」を返します。

※INSTR関数は、全角・半角混在の文字データの場合でもあくまで「文字数」を返します。全角・半角ともに1文字は1としてカウントします。

INSTR関数の書式

INSTR(文字データ,検索文字,検索開始位置,検知回数)

引数	説明
文字データ	対象の文字列
検索文字	検索する文字列
開始位置	検索文字の検索開始位置1 ※開始位置が負の場合は、文字列の末尾から(文頭に向けて)数えた位置が検索開始位置となり、検索も開始位置から文頭に向けて検索する。負の値を指定したとしてもINSTR関数で返す文字数は文字データの先頭から数えた文字数であることに注意。 ※0を指定した場合は常に未検出の「0」を返す。デフォルトは「1」。
検知回数	検索文字の出現回数を指定する例：2を指定した場合は、検索文字が2回目に検知された位置を返す。デフォルトは「1」。

※開始位置・検索位置は省略が可能です。
　省略例：INSTR(文字データ,検索文字)、INSTR(文字データ,検索文字,開始位置)
※検索は大文字・小文字を区別します。検索できなかった場合は「0」を返します。
※検索文字には2文字以上の文字を指定することも可能です。その場合はその文字の最初の1文字が文字データの何文字目にあるかを返します。

1.INSTR関数引数をすべて指定した場合の使用例

INSTR関数のすべての引数を指定したシンプルなSQLで実際の動作を確認しましょう。

--①2つ目の半角スペースの位置検索
SELECT INSTR('ORACLE DATABASE 学習',' ',1,2) FROM DUAL;
--②1つ目のＡの位置検索
SELECT INSTR('ORACLE DATABASE 学習','A',1,1) FROM DUAL;
--③6文字目から検索開始して1つ目のＥの位置検索
SELECT INSTR('ORACLE DATABASE 学習','E',6,1) FROM DUAL;
--④6文字目から検索開始して2つ目のＥの位置検索
SELECT INSTR('ORACLE DATABASE 学習','E',6,2) FROM DUAL;

＜SQLの実行結果＞
f:id:auroralights:20210506234553p:plain

＜解説＞
①～④すべて文字データ「ORACLE DATABASE 学習」に対して検索文字の文字位置を返しています。

①のSQLでは検索文字「半角スペース」、開始位置「1」、検索回数「2」を指定。
文字データの1文字目から検索して2回目に半角スペースが存在する文字位置の文字数を返しています。
②のSQLでは検索文字「A」、開始位置「1」、検索回数「1」を指定。
文字データの1文字目から検索して1回目にAが存在する文字位置の文字数を返しています。
③のSQLでは検索文字「E」、開始位置「6」、検索回数「1」を指定。
文字データの6文字目から検索して1回目にEが存在する文字位置の文字数を返しています。
検索を開始するのは6文字目からですが、INSTR関数で返す文字位置の文字数は文字データの文頭から数えた数値であることを意識しましょう。
④のSQLでは検索文字「E」、開始位置「6」、検索回数「2」を指定。
文字データの6文字目から検索して2回目にEが存在する文字位置の文字数を返しています。

開始位置を何文字目に指定しようが、INSTR関数で返す文字数は、文字データの先頭から数えて何文字目に検索文字が存在するかの文字数であることに注意です。

ここでは試してませんが検索文字に2文字以上の文字を指定した場合も試してみましょう。

2.INSTR関数「全角半角」「大文字小文字」「開始位置が負の値」の使用例

INSTR関数での全角半角、大文字小文字の扱い、開始位置が負の値の場合の動作を実際にSQLを使って確認してみましょう。

--①全角半角の区別(全角スペースの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','　',1,2) FROM DUAL;
--②全角半角の区別(全角Ａの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','Ａ',6,1) FROM DUAL;
--③大文字小文字の区別(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','a',6,1) FROM DUAL;
--④開始位置が負の値の場合(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','Ａ',-1,1) FROM DUAL;
--⑤開始位置が負の値の場合(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','DAT',-5,1) FROM DUAL;

＜SQLの実行結果＞
f:id:auroralights:20210507002158p:plain

＜解説＞
①～⑤すべて文字データ「ORACLE DATABASE 学習」に対して検索文字の文字位置を返しています。

①のSQLでは検索文字「全角スペース」、開始位置「1」、検索回数「2」を指定。
文字データの1文字目から検索して2回目に全角スペースが存在する文字位置の文字数を返しますが、全角スペースはないため「0」を返しています。
②のSQLでは検索文字「Ａ」(全角)、開始位置「6」、検索回数「1」を指定。
文字データの6文字目から検索して1回目にＡ(全角)が存在する文字位置の文字数を返しますが、全角のＡはないため「0」を返しています。
③のSQLでは検索文字「a」、開始位置「6」、検索回数「1」を指定。
文字データの6文字目から検索して1回目にaが存在する文字位置の文字数を返しますが、小文字「a」はないので「0」を返しています。
④のSQLでは検索文字「A」、開始位置「-1」、検索回数「1」を指定。
文字データの末尾の1文字目から検索して1回目にAが存在する文字位置の文字数を返しています。文字数は文頭からカウントします。

f:id:auroralights:20210507003250p:plain