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
