Oracle SQL 関数 : INSTR : 文字列検索
numberRet := INSTR( stringSrc , stringChk , numberPos [ , numberOcr ] ); INSTRは文字列stringSrcのnumberPos番目からstringChk文字列を検索します。 numberOcr回数の検索ができた位置を結果として返します。 (stringSrcの文字列の数え方は先頭が１として処理します。) numberPos及びnumberOcrのデフォルトは１として処理します。 numberPosが正の数の場合はstringSrcの先頭から数え，numberPosが負の数の場合はstringSrcの 最後から数えます。
SQL> SELECT INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','Z',1) AS 回数指定無し |
2 ,INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','A',-1) AS 回数指定無しで逆方向 |
3 ,INSTR('ABCDEFGHIABCDEFGHIABCDEFGHI','A',1,3) AS 回数指定有り |
4 ,INSTR('ABCDEFGHIABCDEFGHIABCDEFGHI','A',2,2) AS 回数指定有りで途中から検索 |
5 FROM DUAL; |
回数指定無し 回数指定無しで逆方向 回数指定有り 回数指定有りで途中から検索 |
------------ -------------------- ------------ -------------------------- |
26 1 19 19 |
実際のテーブルを使用した例を以下に示します。商品マスタを例に取りますが、商品名の中に”－”が
含まれていますが、それを検索して商品名の前半と後半に分ける処理をさせます。最初に商品マスタの一覧を示します。
SQL> select * from TM_商品; |
商品コード 商品名 商品区分 仕入単価 売上単価 |
---------- ---------------------------------------- -------------------- ---------- ---------- |
1 PC-9001 デスクトップパソコン 65000 98000 |
2 PC-9002 デスクトップパソコン 95000 120000 |
3 PC-9003 デスクトップパソコン 150000 190000 |
4 NOTE-1010 ノートパソコン 125000 188000 |
5 NOTE-1020 ノートパソコン 145000 200000 |
6 NOTE-1030 ノートパソコン 155000 220000 |
7 PRT-3001 プリンタ 45000 88000 |
8 PRT-4001 プリンタ 115000 180000 |
9 CRT-1001 ディスプレイ 45000 78000 |
10 CRT-2001 ディスプレイ 55000 98000 |
11 HUB-A001 ネットワーク 20000 40000 |
12 HUB-B001 ネットワーク 40000 60000 |
100 TEST1000 デスクトップパソコン 100000 120000 |
13行が選択されました。 |
SUBSTR関数の引数の中に、INSTRの結果を渡しています。
文字列の中を検索して、文字列の抽出や置換えにはこの様な方法が使えると思います。
SQL> SELECT '#' || SUBSTR(商品名,INSTR(商品名,'-') + 1) || '#' AS 商品名のハイフン以降 |
2 ,'#' || SUBSTR(商品名,1,INSTR(商品名,'-')) || '#' AS 商品名のハイフン以前 |
3 FROM TM_商品; |
商品名のハイフン以降 商品名のハイフン以前 |
---------------------------------------- ---------------------------------------- |
#9001# #PC-# |
#9002# #PC-# |
#9003# #PC-# |
#1010# #NOTE-# |
#1020# #NOTE-# |
#1030# #NOTE-# |
#3001# #PRT-# |
#4001# #PRT-# |
#1001# #CRT-# |
#2001# #CRT-# |
#A001# #HUB-# |
#B001# #HUB-# |
#TEST1000# ## |
13行が選択されました。 |
実際のSQL/PLUSでこのSQLを実行すると、各カラムの長さが非常に長くなり各行が1行では表示できなくなります。
この場合には、PLUSのコマンドのCOLUMNを使用して表示カラムの、表示形式を設定すれば1行で表示可能になります。
SQL> COLUMN 商品名のハイフン以降 FORMAT A40 |
SQL> COLUMN 商品名のハイフン以前 FORMAT A40 |
尚、検索対象文字列、及び検索文字列に 全角文字 が指定された場合は、全角文字も1文字は1個として扱う様です。
SQL> SELECT INSTR('あいうえおかきくけこさしすせそ', 'そ', 1) AS "回数指定無し" |
2 ,INSTR('あいうえおかきくけこさしすせそ', 'あ', -1) AS "回数指定無し・逆方向" |
3 ,INSTR('あいうえおあいうえおあいうえお', 'あ', 1, 3) AS "回数指定有り" |
4 ,INSTR('あいうえおあいうえおあいうえお', 'あ', 2, 2) AS "回数指定有りで途中から検索" |
5 FROM DUAL; |
回数指定無し 回数指定無し・逆方向 回数指定有り 回数指定有りで途中から検索 |
------------ -------------------- ------------ -------------------------- |
15 1 11 11 |
さらに、検索対象文字列、及び検索文字列に 半角、全角文字 が混在していても、うまくいく様です。
SQL> SELECT INSTR('あいうえお123かきくけこabcさしすせそ', 'お1', 1) AS "回数指定無し" |
2 ,INSTR('あいうえお123かきくけこabcさしすせそ', '3か', -1) AS "回数指定無し・逆方向" |
3 ,INSTR('あいうえお123あいうえお123あいうえお123', 'お1', 1, 3) AS "回数指定有り" |
4 ,INSTR('あいうえお123あいうえお123あいうえお123', 'お1', 2, 2) AS "回数指定有りで途中から検索" |
5 FROM DUAL; |
回数指定無し 回数指定無し・逆方向 回数指定有り 回数指定有りで途中から検索 |
------------ -------------------- ------------ -------------------------- |
5 8 21 13 |
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。
