オーロラさんの勉強帳

SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

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

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
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つ目のAの位置検索
SELECT INSTR('ORACLE DATABASE 学習','A',1,1) FROM DUAL;
--③6文字目から検索開始して1つ目のEの位置検索
SELECT INSTR('ORACLE DATABASE 学習','E',6,1) FROM DUAL;
--④6文字目から検索開始して2つ目のEの位置検索
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;
--②全角半角の区別(全角Aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','A',6,1) FROM DUAL;
--③大文字小文字の区別(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','a',6,1) FROM DUAL;
--④開始位置が負の値の場合(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','A',-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では検索文字「A」(全角)、開始位置「6」、検索回数「1」を指定。
    文字データの6文字目から検索して1回目にA(全角)が存在する文字位置の文字数を返しますが、全角のAはないため「0」を返しています。
  • ③のSQLでは検索文字「a」、開始位置「6」、検索回数「1」を指定。
    文字データの6文字目から検索して1回目にaが存在する文字位置の文字数を返しますが、小文字「a」はないので「0」を返しています。
  • ④のSQLでは検索文字「A」、開始位置「-1」、検索回数「1」を指定。
    文字データの末尾の1文字目から検索して1回目にAが存在する文字位置の文字数を返しています。文字数は文頭からカウントします。

f:id:auroralights:20210507003250p:plain

  • ⑤のSQLでは検索文字「DAT」、開始位置「-5」、検索回数「1」を指定。
    文字データの末尾5文字目から検索して1回目にDATが存在する文字位置の文字数を返しています。

f:id:auroralights:20210507003500p:plain


INSTR関数は大文字小文字、全角半角を区別します。
開始位置が負の値の場合、検索は文末から文頭に検索をしますが、INSTR関数で返す文字位置については、必ず文頭からの文字数を返すことに注意です。

【Excel】【新社会人必須】VLOOKUP関数の使い方 ~簡単・わかりやすいVLOOKUP関数入門~

Excelを使う仕事でかかせない関数の一つがVLOOKUP関数だと思います。
新社会人やあまりExcelを使う機会がなかった方などに向けて、VLOOKUP関数の使い方を紹介します。


目次

VLOOKUP関数とは

VLOOKUP関数は、範囲内の一番左側の列に検索値がある場合、検索値から指定した列数分右側のデータを返す関数です。


以下のように商品コードと価格の対照表(商品価格マスタ)と、各担当者が販売した実績表があり、商品コードに対応する価格を入力する場合などにVLOOKUPL関数を使います。
f:id:auroralights:20210503232721p:plain

VLOOKUP関数の構文

VLOOKUP関数の構文は以下の通りです。
=VLOOKUP(検索値,範囲,列番号,検索方法)


VLOOKUP関数の引数について

検索値 検索する値
範囲 検索値を検索するセル範囲。
検索値は範囲の一番左側の列に必ず位置する必要があります。
列番号 検索値から何列目のデータを返すかを指定します。
検索方法 近似値一致の場合はTRUE。完全一致の場合はFALSE(0)を指定します。
基本は完全一致で使うのでFALSEもしくは0を指定します。
規定値はTRUEです。


※範囲は絶対参照をすることが多いです。「F4」キーで絶対参照をすると覚えておきましょう。
※検索方法については、完全一致のFALSEもしくは0を指定すると覚えておいてください。


以下の例ではセル「H2」に「=VLOOKUP(G2,$A$1:$C$10,3,FALSE)」の数式が入っています。
f:id:auroralights:20210503230034p:plain

引数は以下のように指定しています。

検索値 G2
範囲 $A$1:$C$10
列番号 3
検索方法 FALSE

考え方としては、範囲の「$A$1:$C$10」の左端の列(A列)に検索値「G2」(A00009)があれば、そのデータの3列右のデータを返します。
A0009の3列右のデータなので「高橋 花子」をVLOOKUP関数で返しています。

VLOOKUP関数を使ってみよう

以下のように商品価格マスタ(商品コード・価格対照表)、担当者マスタ(担当者ID
ID・担当者名対照表)、販売実績テーブルの3つの表があります。
販売実績テーブルに担当者IDに紐づく担当者名を、商品コードに紐づく価格をVLOOKUP関数で入力してみましょう。

f:id:auroralights:20210503234806p:plain

担当者名をVLOOKUP関数で求める方法

セルH2に「=VLOOKUP(G3,$D$3:$E$6,2,FALSE)」と数式を入力します。
f:id:auroralights:20210504000911p:plain

検索値「G3」と完全一致するデータがセル範囲「$D$3:$E$6」の左端の列にある場合、2列目のデータを返します。
担当者ID「A0001」に対応する担当者名「佐藤 一郎」が返ってきています。
f:id:auroralights:20210503235422p:plain


次にH3のデータを表の一番下までコピーします。
この時に「範囲」を絶対参照で指定していないと範囲がずれてしまうので注意しましょう。
f:id:auroralights:20210504000121p:plain
コピーする方法については、ドラッグアンドドロップでもいいですし、Ctrl+C、Ctrl+Vでコピーする方法、セル「H3」右下にカーソルと合わせて、カーソルが十字になったときにダブルクリックする方法などでコピーします。

VLOOKUP関数で商品コードに対応する価格を求める方法

セル「K3」に「=VLOOKUP(I3,$A$3:$B$8,2,FALSE)」と数式を入力し、販売実績テーブルの最下行までコピーします。担当者名を取得した方法と同じです。
f:id:auroralights:20210504001204p:plain


以上、VLOOKUP関数の使い方を紹介しました。
VLOOKUP関数の応用として、MATCH関数と組み合わせて使う方法もあります。
以下の記事も参考にしてみてください。
auroralights.jp

【SQL】CONCAT関数 (ORACLE DATABASE 12C SQL基礎)

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


目次

CONCAT関数の使い方

CONCAT関数は引数で指定した2つの文字データを結合します。
文字列結合の連結演算子「||」と同じ使い方ができます。
CONCAT関数は2つの文字列の結合に対してと制限があるのに対して、連結演算子「||」は文字列データの個数に制限なく結合ができます。

CONCAT関数の書式

CONCAT(文字データ)

CONCAT(文字データ)の使用例

--2つの文字データを結合する例
SELECT CONCAT('ORACLE','DATABASE') FROM DUAL;
SELECT CONCAT('ORACLE','1Z0-061') FROM DUAL;


上記SQLを実行すると2つの文字列データを結合した値が返ってきます。
f:id:auroralights:20210424223555p:plain


2つの以上の引数を指定すると、以下のように「ORA-00909:引数の個数が無効です」とエラーが返されます。
f:id:auroralights:20210430015314p:plain