Oracle SQL

Oracle で ILIKE 関数が使えない代わりに、REGEXP_LIKE 関数を使おう

Oracle データベースでは ILIKE 関数を使えないため、LIKE や ILIKE 関数を使って大文字小文字を無視して文字列の検索を行うことが出来ません。しかし REGEXP_LIKE 関数を使うことにより文字列のパターンマッチングを行うことが出来ますので、REGEXP_LIKE 関数を利用するようにしましょう。REGEXP_LIKE 関数の使い方を覚えてしまえば、もっと柔軟なパターンマッチングが出来るようになりますので、ここで使い方を覚えましょう。

REGEXP_LIKE 関数とは？

REGEXP_LIKE 関数は機能的に LIKE 関数に非常に似通っています。しかし LIKE はシンプルな文字列一致検索が可能ですが、REGEXP_LIKE 関数は文字列一致検索に加えてさらに正規表現を使えるため非常に強力な機能を有しています。

REGEXP_LIKE は次のようにして使うことが出来ます。

REGEXP_LIKE ( string , pattern [, match] )

つまり

REGEXP_LIKE ( 検索される対象 , 検索文字列 [, 検索パラメータ] )

として利用可能です。"[, 検索パラメータ]" は省略可能です。

検索パラメータはオプションで、"検索される対象" と "検索文字列" の２つだけでも利用することが可能ですが、以下のような検索パラメータを利用すると、より柔軟な検索が可能となります。

'i' 大文字小文字を区別しない

specifies case-insensitive matching. Ignore case.

'c' 大文字小文字を区別する

specifies case-sensitive matching.

'n' ピリオド(.)で改行文字にも一致させる

allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, the period does not match the newline character.

'm' 検索対象を複数行とみなして "^" と "$" を各行の先頭と終了に一致させる

treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.

様々な検索パラメータがありますが、ILIKE 関数の代わりに大文字小文字を区別しないようにしたいだけであれば、検索パラメータの "i" を使うだけです。

REGEXP_LIKE 関数を使って大文字小文字を無視して検索する

ILIKE 関数のように大文字小文字を無視して文字列の部分一致検索を行ってみましょう。まず最初に以下のような message_table というテーブルを作成し、３件のテストデータを INSERT します。全てのデータには "need" という文字列が含まれています (needs, NEED, NeeD)。

 CREATE TABLE message_table
(
  key_message VARCHAR2(50) NOT NULL
);

INSERT INTO message_table VALUES ('Data Discrepancy needs to be fixed.');
INSERT INTO message_table VALUES ('I am in desperate NEED of money.');
INSERT INTO message_table VALUES ('I really NeeD you forever.');

このデータに対して、大文字小文字を無視して文字列検索を行うには、LOWER() 関数を使って全て小文字で比較するか、UPPER() 関数を使って全て大文字で比較することになります。以下は実際に LOWER() 関数で全て小文字に変換して比較した場合の例です。

"need" という文字列が含まれるものを全て検索する例です。

SELECT
  key_message AS key_message_like
FROM message_table
WHERE LOWER(key_message) LIKE '%need%';

KEY_MESSAGE                                      
--------------------------------------------------
Data Discrepancy needs to be fixed.               
I am in desperate NEED of money.                  
I really NeeD you forever.

次に REGEXP_LIKE() 関数を使って大文字小文字を無視して比較した場合の SQL です。検索パラメータとして "i" (Ignore Case) を指定して、大文字小文字の区別をせずに比較しています。前述の LOWER() 関数を使った場合と同じ結果が得られています。

SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, 'need', 'i');

KEY_MESSAGE_REGEXP_LIKE                          
--------------------------------------------------
Data Discrepancy needs to be fixed.               
I am in desperate NEED of money.                  
I really NeeD you forever.

REGEXP_LIKE() 関数で正規表現を使ってみる

前述のように REGEXP_LIKE() 関数は ILIKE() 関数の代わりとして使えることが分かりました。しかし REGEXP_LIKE() 関数の正規表現機能は更に強力で、以下のように "am" または "NeeD" だけが含まれる文字列を OR 条件で検索することが可能です。"|" が "OR" 条件を示します。

SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, 'am|NeeD');

KEY_MESSAGE_REGEXP_LIKE                          
--------------------------------------------------
I am in desperate NEED of money.                  
I really NeeD you forever.

REGEXP_LIKE() 関数で様々な条件で柔軟にデータを検索する

OR 条件

-- OR 条件 ("am" または "NeeD" が含まれるものだけを選択)
-- "|" は OR 条件という意味です。
SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, 'am|NeeD');

KEY_MESSAGE_REGEXP_LIKE
--------------------------------------------------
I am in desperate NEED of money.
I really NeeD you forever.

AND 条件

-- AND 条件 ("really" と "you" 両方が含まれるものだけを選択)
-- "()|()" は AND 条件という意味です。
SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, '(really)|(you)');

KEY_MESSAGE_REGEXP_LIKE
--------------------------------------------------
I really NeeD you forever.

SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, '(you)|(really)');

KEY_MESSAGE_REGEXP_LIKE
--------------------------------------------------
I really NeeD you forever.

行の先頭がある文字列で始まるものを検索

-- "I" から始まる文字列で、途中に "you" が含まれる文字列だけを選択。
-- "^" 記号は文字列の先頭を指す特殊文字です。
-- ".*" は任意の文字列を指します。つまりどんな文字にでも一致するという意味です。
SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, '^I.*you.*');

KEY_MESSAGE_REGEXP_LIKE
--------------------------------------------------
I really NeeD you forever.

行の末尾がある文字列で終わるものを検索

-- "I" から始まる文字列で、"money." で終わる文字列だけを選択。
-- "^" 記号は文字列の先頭を指す特殊文字です。
-- "$" 記号は文字列の末尾を指す特殊文字です。
SELECT
  key_message AS key_message_regexp_like
FROM message_table
WHERE REGEXP_LIKE(key_message, '^I.*money.$');

KEY_MESSAGE_REGEXP_LIKE
--------------------------------------------------
I am in desperate NEED of money.

このように REGEXP_LIKE() 関数は使いこなすと非常に強力な検索が可能となります。ぜひ活用しましょう。

Everything you do is practice

Oracle で ILIKE 関数が使えない代わりに、REGEXP_LIKE 関数を使おう