-- 基本語法（最常用）
INSTR(string, substring [, start_position [, occurrence]])
-- 用于雙字節(jié)字符環(huán)境（如中文）
INSTRB(string, substring [, start_position [, occurrence]])

-- 基礎(chǔ)示例
SELECT 
    INSTR('Oracle Database', 'a') AS pos1,        -- 返回: 3（第一個'a'的位置）
    INSTR('Oracle Database', 'a', 1, 2) AS pos2,  -- 返回: 10（第二個'a'的位置）
    INSTR('Oracle Database', 'z') AS pos3         -- 返回: 0（未找到）
FROM dual;

-- 正向搜索（默認行為）
SELECT 
    -- 從位置1開始搜索
    INSTR('Searching in this string', 'in', 1) AS pos1,     -- 返回: 10
    -- 從位置11開始搜索（跳過前10個字符）
    INSTR('Searching in this string', 'in', 11) AS pos2,    -- 返回: 13
    -- 使用負起始位置：從右向左搜索
    INSTR('Searching in this string', 'in', -1) AS pos3,    -- 返回: 20（從末尾開始）
    -- 從倒數(shù)第5個字符開始向左搜索
    INSTR('Searching in this string', 'in', -5) AS pos4     -- 返回: 13
FROM dual;

-- 查找特定次數(shù)的出現(xiàn)
SELECT 
    -- 查找第一個'in'
    INSTR('in the beginning and in the end', 'in', 1, 1) AS first_in,  -- 返回: 1
    -- 查找第二個'in'
    INSTR('in the beginning and in the end', 'in', 1, 2) AS second_in, -- 返回: 20
    -- 查找第三個'in'（不存在）
    INSTR('in the beginning and in the end', 'in', 1, 3) AS third_in,  -- 返回: 0
    -- 結(jié)合負起始位置：從右向左找第一個'in'
    INSTR('in the beginning and in the end', 'in', -1, 1) AS last_in   -- 返回: 20
FROM dual;

-- 創(chuàng)建測試數(shù)據(jù)
WITH test_data AS (
    SELECT '中國北京' AS chinese_string FROM dual
)
SELECT 
    -- INSTR：基于字符計數(shù)
    INSTR(chinese_string, '北京') AS char_position,      -- 返回: 3
    -- INSTRB：基于字節(jié)計數(shù)（UTF-8示例）
    INSTRB(chinese_string, '北京') AS byte_position      -- 返回: 7（如果每個中文字符3字節(jié)）
FROM test_data;
-- 實際數(shù)據(jù)庫中的驗證
SELECT 
    '測試字符串' AS test_str,
    LENGTH('測試字符串') AS char_length,      -- 返回: 5
    LENGTHB('測試字符串') AS byte_length,     -- 返回: 15（如果UTF-8，每個中文3字節(jié)）
    INSTR('測試字符串', '字符串') AS instr_char,  -- 返回: 3
    INSTRB('測試字符串', '字符串') AS instr_byte   -- 返回: 7
FROM dual;

-- 1. 驗證郵箱格式（必須包含@符號）
SELECT 
    email,
    CASE 
        WHEN INSTR(email, '@') > 0 THEN 'Valid'
        ELSE 'Invalid'
    END AS validation_status
FROM users;
-- 2. 提取域名部分
SELECT 
    email,
    -- 提取@之后的部分
    SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM users
WHERE INSTR(email, '@') > 0;
-- 3. 復(fù)雜驗證：檢查是否符合特定模式
SELECT 
    phone_number,
    CASE 
        -- 檢查是否包含非法字符
        WHEN INSTR(phone_number, '--') > 0 THEN 'Invalid: contains double hyphen'
        -- 檢查括號是否匹配
        WHEN INSTR(phone_number, '(') > 0 AND INSTR(phone_number, ')') = 0 
            THEN 'Invalid: missing closing parenthesis'
        ELSE 'Valid'
    END AS phone_validation
FROM customers;

-- 解析HTTP日志，提取關(guān)鍵信息
SELECT 
    log_entry,
    -- 提取HTTP方法
    SUBSTR(log_entry, 1, INSTR(log_entry, ' ') - 1) AS http_method,
    -- 提取請求路徑（第一個空格到第二個空格之間）
    SUBSTR(
        log_entry,
        INSTR(log_entry, ' ') + 1,
        INSTR(log_entry, ' ', 1, 2) - INSTR(log_entry, ' ') - 1
    ) AS request_path,
    -- 提取狀態(tài)碼（倒數(shù)第三個空格后的3位數(shù)字）
    TO_NUMBER(
        SUBSTR(
            log_entry,
            INSTR(log_entry, ' ', -1, 2) + 1,
            3
        )
    ) AS status_code,
    -- 檢查是否包含錯誤
    CASE 
        WHEN INSTR(UPPER(log_entry), 'ERROR') > 0 THEN 'ERROR'
        WHEN INSTR(UPPER(log_entry), 'WARN') > 0 THEN 'WARNING'
        ELSE 'INFO'
    END AS log_level
FROM web_server_logs
WHERE ROWNUM <= 10;

-- 1. 智能字段分割
CREATE OR REPLACE PROCEDURE parse_delimited_string(
    p_input_string VARCHAR2,
    p_delimiter VARCHAR2 DEFAULT ','
) AS
    v_start_pos NUMBER := 1;
    v_end_pos NUMBER;
    v_token VARCHAR2(4000);
    v_token_num NUMBER := 1;
BEGIN
    LOOP
        -- 查找下一個分隔符
        v_end_pos := INSTR(p_input_string, p_delimiter, v_start_pos);
        IF v_end_pos = 0 THEN
            -- 最后一個令牌
            v_token := SUBSTR(p_input_string, v_start_pos);
            DBMS_OUTPUT.PUT_LINE('Token ' || v_token_num || ': ' || v_token);
            EXIT;
        ELSE
            -- 提取當前令牌
            v_token := SUBSTR(p_input_string, v_start_pos, v_end_pos - v_start_pos);
            DBMS_OUTPUT.PUT_LINE('Token ' || v_token_num || ': ' || v_token);
            -- 更新起始位置，跳過分隔符
            v_start_pos := v_end_pos + LENGTH(p_delimiter);
            v_token_num := v_token_num + 1;
        END IF;
    END LOOP;
END;
/
-- 2. 執(zhí)行示例
BEGIN
    parse_delimited_string('apple,banana,orange,grape');
    -- 輸出:
    -- Token 1: apple
    -- Token 2: banana
    -- Token 3: orange
    -- Token 4: grape
END;
/

-- 雖然Oracle有REGEXP_INSTR，但INSTR在簡單場景中更高效
SELECT 
    phone_number,
    -- 使用INSTR檢查是否包含區(qū)號
    CASE 
        WHEN INSTR(phone_number, '(') = 1 AND 
             INSTR(phone_number, ')') = 5 THEN 'Has area code'
        ELSE 'No area code'
    END AS area_code_check,
    -- 與REGEXP_INSTR的比較
    REGEXP_INSTR(phone_number, '^\(\d{3}\)') AS regex_pos
FROM customer_contacts;
-- 性能對比：INSTR通常比正則表達式更快
EXPLAIN PLAN FOR
SELECT * FROM large_table 
WHERE INSTR(description, 'urgent') > 0;
EXPLAIN PLAN FOR  
SELECT * FROM large_table
WHERE REGEXP_LIKE(description, 'urgent');
-- INSTR使用普通索引，REGEXP通常不能有效使用索引

-- 解析嵌套結(jié)構(gòu)字符串
WITH nested_data AS (
    SELECT '[user:{id:123,name:"John"},session:{id:"abc123"}]' AS json_like FROM dual
)
SELECT 
    json_like,
    -- 查找第一個user對象的開始
    INSTR(json_like, 'user:{') AS user_start,
    -- 查找對應(yīng)的結(jié)束位置（找到匹配的}）
    -- 這是一個簡化實現(xiàn)，實際需要處理嵌套
    INSTR(json_like, '}', INSTR(json_like, 'user:{')) AS user_end,
    -- 提取user對象內(nèi)容
    SUBSTR(
        json_like,
        INSTR(json_like, 'user:{') + 6,  -- 'user:{'長度為6
        INSTR(json_like, '}', INSTR(json_like, 'user:{')) - 
        (INSTR(json_like, 'user:{') + 6)
    ) AS user_content
FROM nested_data;

-- 使用INSTR優(yōu)化LIKE查詢
-- 原始查詢（可能不會使用索引）
SELECT * FROM products 
WHERE product_name LIKE '%premium%';
-- 優(yōu)化版本（如果存在product_name上的函數(shù)索引）
CREATE INDEX idx_product_name_instr ON products(INSTR(product_name, 'premium'));
SELECT * FROM products 
WHERE INSTR(product_name, 'premium') > 0;
-- 性能對比測試
SET TIMING ON;
-- 測試LIKE
SELECT COUNT(*) FROM large_text_table WHERE text_content LIKE '%specific_term%';
-- 測試INSTR  
SELECT COUNT(*) FROM large_text_table WHERE INSTR(text_content, 'specific_term') > 0;
SET TIMING OFF;

-- 綜合使用示例：提取郵箱用戶名
SELECT 
    email,
    -- 使用INSTR找到@位置，然后用SUBSTR提取
    SUBSTR(email, 1, INSTR(email, '@') - 1) AS username,
    -- 僅使用SUBSTR和INSTR
    INSTR(email, '@') AS at_position,
    -- 使用LIKE驗證格式
    CASE 
        WHEN email LIKE '%@%.%' THEN 'Valid format'
        ELSE 'Invalid format'
    END AS format_check
FROM users;

-- 智能字符串分類
SELECT 
    document_text,
    CASE
        -- 檢查是否包含多個關(guān)鍵詞
        WHEN INSTR(document_text, 'confidential') > 0 AND
             INSTR(document_text, 'internal use') > 0 THEN 'Highly Restricted'
        WHEN INSTR(document_text, 'draft') > 0 THEN 'Draft Document'
        WHEN INSTR(document_text, 'final') > 0 AND
             INSTR(document_text, 'approved') > 0 THEN 'Approved Final'
        -- 使用INSTR的occurrence參數(shù)
        WHEN INSTR(document_text, 'urgent', 1, 2) > 0 THEN 'Double Urgent'
        WHEN INSTR(document_text, 'urgent') > 0 THEN 'Urgent'
        ELSE 'Normal'
    END AS document_priority,
    -- 計算關(guān)鍵詞出現(xiàn)次數(shù)（簡化方法）
    (LENGTH(document_text) - 
     LENGTH(REPLACE(document_text, 'urgent', ''))) / 
     LENGTH('urgent') AS urgent_count
FROM documents;

-- 1. 為INSTR創(chuàng)建函數(shù)索引
CREATE INDEX idx_desc_search ON products(INSTR(description, 'premium'));
-- 查詢時使用相同的表達式
SELECT * FROM products 
WHERE INSTR(description, 'premium') > 0;
-- 2. 避免在WHERE子句中對列進行函數(shù)包裝（壞例子）
SELECT * FROM products 
WHERE INSTR(UPPER(description), UPPER('premium')) > 0;  -- 無法使用索引
-- 3. 更好的做法：存儲時統(tǒng)一大小寫或創(chuàng)建函數(shù)索引
CREATE INDEX idx_desc_upper ON products(INSTR(UPPER(description), 'PREMIUM'));
-- 4. 分區(qū)表上的應(yīng)用
CREATE TABLE log_messages (
    id NUMBER,
    message VARCHAR2(4000),
    created_date DATE
)
PARTITION BY RANGE (created_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (DATE '2023-04-01'),
    PARTITION p_2023_q2 VALUES LESS THAN (DATE '2023-07-01')
);
-- 創(chuàng)建本地索引
CREATE INDEX idx_log_msg_search ON log_messages(INSTR(message, 'ERROR')) LOCAL;

-- 陷阱1：空字符串的處理
SELECT 
    INSTR('test', '') AS empty_search,      -- 返回: 1（可能不符合預(yù)期）
    INSTR('', 'test') AS empty_string       -- 返回: 0
FROM dual;
-- 解決方案：顯式檢查
SELECT *
FROM table
WHERE column_value IS NOT NULL 
  AND column_value != ''
  AND INSTR(column_value, 'search_term') > 0;
-- 陷阱2：開始位置超出字符串長度
SELECT 
    INSTR('short', 's', 10) AS beyond_length  -- 返回: 0
FROM dual;
-- 陷阱3：多字節(jié)字符的誤用
SELECT 
    INSTR('café', 'é') AS single_byte,        -- 可能返回正確位置
    INSTRB('café', 'é') AS multi_byte         -- 可能需要考慮編碼
FROM dual;
-- 最佳實踐：始終考慮字符集
SELECT 
    text_data,
    INSTR(text_data, '搜索詞') AS char_pos,
    INSTRB(text_data, '搜索詞') AS byte_pos,
    CASE 
        WHEN INSTR(text_data, '搜索詞') > 0 THEN 'Found'
        ELSE 'Not found'
    END AS search_result
FROM multilingual_data;

-- 監(jiān)控數(shù)據(jù)質(zhì)量問題
CREATE OR REPLACE VIEW data_quality_issues AS
SELECT 
    'employees' AS table_name,
    employee_id,
    'email_missing_at' AS issue_type,
    email AS problem_value
FROM employees
WHERE INSTR(email, '@') = 0
UNION ALL
SELECT 
    'products',
    product_id,
    'multiple_delimiters',
    product_code
FROM products
WHERE INSTR(product_code, '||') > 0
UNION ALL
SELECT 
    'orders',
    order_id,
    'suspicious_pattern',
    comments
FROM orders
WHERE INSTR(comments, '###') > 0
   OR INSTR(comments, 'XXX') > 0;
-- 定期運行數(shù)據(jù)質(zhì)量檢查
BEGIN
    FOR issue IN (SELECT * FROM data_quality_issues WHERE ROWNUM <= 10) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Table: ' || issue.table_name ||
            ', ID: ' || issue.employee_id ||
            ', Issue: ' || issue.issue_type
        );
    END LOOP;
END;
/

-- 實現(xiàn)高級搜索功能
CREATE OR REPLACE FUNCTION smart_search(
    p_search_text VARCHAR2,
    p_content VARCHAR2
) RETURN NUMBER AS
    v_score NUMBER := 0;
    v_term VARCHAR2(100);
    v_delimiters VARCHAR2(10) := ' ,.;:!?';
    v_start_pos NUMBER := 1;
    v_end_pos NUMBER;
BEGIN
    -- 簡單搜索：完全匹配
    IF INSTR(p_content, p_search_text) > 0 THEN
        v_score := v_score + 100;
    END IF;
    -- 分詞搜索
    WHILE v_start_pos <= LENGTH(p_search_text) LOOP
        v_end_pos := LENGTH(p_search_text) + 1;
        -- 查找下一個分隔符
        FOR i IN 1..LENGTH(v_delimiters) LOOP
            v_end_pos := LEAST(
                v_end_pos, 
                NVL(INSTR(p_search_text, SUBSTR(v_delimiters, i, 1), v_start_pos), 
                    LENGTH(p_search_text) + 1)
            );
        END LOOP;
        v_term := SUBSTR(p_search_text, v_start_pos, v_end_pos - v_start_pos);
        IF LENGTH(v_term) > 2 THEN
            IF INSTR(p_content, v_term) > 0 THEN
                v_score := v_score + 50;
            END IF;
        END IF;
        v_start_pos := v_end_pos + 1;
    END LOOP;
    RETURN v_score;
END;
/
-- 使用智能搜索
SELECT 
    document_title,
    document_content,
    smart_search('database security audit', document_content) AS relevance_score
FROM documents
WHERE smart_search('database security audit', document_content) > 0
ORDER BY relevance_score DESC;