Oracleでの文字列抽出手法の総合的なまとめ

substr関数：文字列の一部を抽出する 構文：SUBSTR(文字列, 開始位置, [長さ])

文字列：抽出元となる元の文字列です。

開始位置：抽出の開始位置で、1から数えます。負の値の場合は文字列末尾から数えます。

長さ：省略可能で、抽出する文字数を指定します。

例：

SELECT SUBSTR('Hello SQL!', 1) FROM dual     -- 全文字列を抽出し、'Hello SQL!'を返す
SELECT SUBSTR('Hello SQL!', 2) FROM dual     -- 2番目の文字から末尾まで抽出し、'ello SQL!'を返す
SELECT SUBSTR('Hello SQL!', -4) FROM dual    -- 末尾から4番目の文字から末尾まで抽出し、'SQL!'を返す
SELECT SUBSTR('Hello SQL!', 3, 6) FROM dual  -- 3番目から6文字を抽出し、'llo SQ'を返す
SELECT SUBSTR('Hello SQL!', -4, 3) FROM dual -- 末尾から4番目から3文字を抽出し、'SQL'を返す

instr関数：部分文字列が元の文字列内でどこに存在するかを返す 構文：INSTR(文字列, 部分文字列, [開始位置], [出現回数])

文字列：検索対象の元の文字列です。

部分文字列：検索する文字列です。

開始位置：省略可能で、検索を開始する位置。デフォルトは1。負の値の場合は右側から検索します。

出現回数：部分文字列が何回目に現れるかを指定。デフォルトは1。負の値はエラーになります。

例：

-- 'city_company_staff'という文字列から、最初の文字から'_'が最初に現れる位置を探す SELECT INSTR('city_company_staff', '_') FROM dual -- 結果は5

-- 'city_company_staff'という文字列から、5文字目から'_'が最初に現れる位置を探す SELECT INSTR('city_company_staff', '_', 5) FROM dual -- 結果は5

-- 'city_company_staff'という文字列から、5文字目から'_'が最初に現れる位置を探す SELECT INSTR('city_company_staff', '_', 5, 1) FROM dual -- 結果は5

-- 'city_company_staff'という文字列から、3文字目から'_'が2回目に現れる位置を探す SELECT INSTR('city_company_staff', '_', 3, 2) FROM dual -- 結果は13

-- 開始位置を-1として右から検索し、'_'が最初に現れる位置を探す SELECT INSTR('city_company_staff', '_', -1, 1) FROM dual -- 結果は13

-- 開始位置を-6として右から検索し、'_'が2回目に現れる位置を探す SELECT INSTR('city_company_staff', '_', -6, 2) FROM dual -- 結果は5

substr関数とinstr関数を組み合わせた文字列抽出 ある課題：コードを「分割」して処理する必要があるとします。

コードの命名規則は以下のようになっています：都市_所属会社_職位_氏名

各要素（都市、会社、職位、氏名）の文字数は不定です。文字数が不定なため単純にsubstr関数だけでは抽出できません。そのため、instr関数で'_'の位置を特定し、その後にsubstr関数を使って必要な部分を抽出します。以下に具体的な例を示します。

テーブルのデータは以下の通りです：

SOURCE_CODE BJ_BAIDU_CEO_LY SH_BOKE_MANAGER_LWX HRB_WM_CASHIER_OYZY

都市を取得する場合：

SELECT  
 SUBSTR (SOURCE_CODE, 1, INSTR (SOURCE_CODE, '_', 1, 1) - 1) AS CITY  
FROM  
 TABLE_CODE_TEST  

結果：

解説：元の文字列SOURCE_CODEの1文字目から開始し、都市名の文字数は不定なので、INSTR関数を使って最初の'_'の位置を特定し、そこまでの文字列を抽出しています。

なぜ-1をするのか？

INSTR(SOURCE_CODE, '_', 1, 1)は最初の'_'の位置を返します。この値から1を引くことで、都市名の文字数が得られます。

会社を取得する場合：

SELECT
    SUBSTR (
        SOURCE_CODE,
        INSTR (SOURCE_CODE, '_', 1, 1) + 1,
        INSTR (SOURCE_CODE, '_', 1, 2) - INSTR (SOURCE_CODE, '_', 1, 1)-1
    ) AS COMPANY
FROM
    TABLE_CODE_TEST

結果：

解説：最初の'_'の位置+1から開始し、その長さを第2の'_'の位置から第1の'_'の位置を引いた値で決定します。ただし、'_'が含まれてしまうので、さらに1を引いて会社名のみを抽出しています。

氏名を取得する場合：

SELECT  
 SUBSTR (SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 3) + 1) AS STF_NAME  
FROM  
 TABLE_CODE_TEST  

結果：

解説：3回目に現れる'_'の位置+1から末尾までを抽出しています。