SQLのINSTR関数で文字列を検索！

2024.12.17 株式会社システムインテグレータ

今回の初心者訓練所#22は、文字列の操作に便利なINSTR関数について説明していきます。他の関数との組合せ等でも、色々な文字列操作が可能になる便利な関数なので、是非、使い方をマスターしてください。いつもの「SQLite」を使用して進めていきますので、「SQLite」をインストールして、実際に試しながら読み進めてみてください。

INSTR関数

INSTR関数は、文字列X内で文字列Yが最初に出現する位置を検索し、その前の文字数に1を加えた数を返します。文字列Yが文字列X内で見つからない場合は 0 を返します。また、文字列Xまたは文字列YのいずれかがNULLの場合、結果はNULLになります。
構文は以下となります。

INSTR ( 文字列X , 文字列Y )

それでは、実際のデータを利用して確認するために、確認用の社員テーブル(tbl_employees)を作成してデータを挿入してみましょう。

create table tbl_employees (emp_code integer, emp_name text, kana text , email text , gender text , birthday text , dept_code integer);

insert into tbl_employees values
(101,'HoriiTaro','ﾎﾘｲﾀﾛｳ','horii@test.co.jp','M','1970-12-27',100)
, (102,'IshimuraHana','ｲｼﾑﾗﾊﾅ','ishimura@test.co.jp','F','1981-09-01',200)
, (103,'KitajimaRuna','ｷﾀｼﾞﾏﾙﾅ','kitajima@test.co.jp','F','1981-11-30',300)
, (104,'MuroiYuri','ﾑﾛｲﾕﾘ','muroi@test.co.jp','F','1982-02-12',400)
, (105,'KitamuraAika','ｷﾀﾑﾗｱｲｶ','kitamura@test.co.jp','F','1980-08-05',400)
, (106,'MitamuraKoukiti','ﾐﾀﾑﾗｺｳｷﾁ','mitamura@test.co.jp','M','1979-03-16',200);

blog-22-01

出力フォーマットを調整して、SELECT文でそれぞれのテーブルの登録内容を確認してみましょう。

.headers on　（ヘッダ情報を出力）
.mode column　（カラム毎に左揃え出力）

select * from tbl_employees;

blog-22-02
社員テーブルに、6件のデータが作成されていることが確認できます。

それでは、作成したデータからINSTR関数を使用して、社員テーブルの社員名(emp_name)で「a」の文字が最初に出現する位置を取得してみましょう。

以下のSELECT文で確認してみましょう。

select emp_code,emp_name,INSTR(emp_name, 'a' )
from tbl_employees;

それぞれのデータで、「a」の文字が最初に登場する位置が表示されていることが確認できます。また、社員コード＝104のように、「a」の文字が含まれていない場合は、0が返されていることも確認できます。

同様に次は、「mura」という文字列が最初に出現する位置を確認してみましょう。

select emp_code,emp_name,INSTR(emp_name, 'mura' )
from tbl_employees;

blog-22-04

「mura」の文字列が最初に登場する位置が表示され、「mura」が含まれていない場合は０が返されていることが確認できます。
また、以下のように全角文字列の場合も1文字が1とカウントされますので、全角半角を意識せずに使用できます。

select INSTR('堀井太郎' , '太郎' ) AS result;

blog-22-05

他の関数や句との組合せ例

続きまして、他の関数や句と組み合わせて使用する例をいくつか紹介していきます。

（１）SUBSTR関数との組合せ
　SUBSTR関数と組合せて、Eメール(email)の「@」より前の文字列を切り出します。

select email , SUBSTR(email, 1, INSTR(email, '@' ) - 1) AS local
from tbl_employees;

blog-22-06

INSTR関数で「@」の位置を求め、SUBSTR関数で先頭から「@」の位置の1つ前までの文字列を切り出しています。

（２）LENGTH関数との組合せ
　上記（１）に、更にLENGTH関数を追加して、切り出した文字列の長さを求めます。

select email
, SUBSTR(email, 1, INSTR(email, '@' ) - 1) AS local
, LENGTH(SUBSTR(email, 1, INSTR(email, '@' ) - 1)) AS length
from tbl_employees;

blog-22-07

切り出した文字列の長さを取得していることが確認できます。

（３）TRIM関数との組合せ
　空白文字を除去したあとに、文字列の位置を取得します。

select INSTR(' Horii Taro ' , 'Taro' ) AS befor
,INSTR(TRIM(' Horii Taro ') , 'Taro' ) AS after;

blog-22-08

テーブルを使用せずに直接文字列を記載していますが、対象の文字列は先頭に空白2文字、後方に空白3文字がある状態となっています。
TRIM関数を使用しない場合(befor)は。空白を含めて「Taro」の開始位置が 9 となり、TRIM関数を使用した場合(after)は、先頭の空白2文字を除去した位置の 7 となっています。

（４）CASE句との組合せ
　CASE句の条件で使用して、文字列の存在チェックで処理を分岐させます。

select emp_code,emp_name
,CASE WHEN INSTR(emp_name , 'Ki' ) > 0 THEN 'yes'
ELSE 'none'
END AS result
from tbl_employees;

blog-22-09
CASE句の条件式の中でINSTR関数を使用して、emp_nameに文字列「Ki」が含まれている場合は、resultに「yes」を、それ以外は「none」を設定しています。

説明は以上となります。

あとがき

「初心者訓練所」の第22弾では、INSTR関数について説明してきました。INSTR関数は単独では使用する機会が少ないかもしれませんが、今回、紹介しましたように他の関数や句と組合せることにより、文字列操作の柔軟性が向上するとても便利な関数です。これらの例を参考にして、必要に応じてさまざまな組み合わせを試してみてください。

(株)システムインテグレータ　TOPSICチーム　元鬼軍曹　久保　司