Oracle数据库INSTR函数详解(数据库中的字符串搜索神器)

-- 基本语法（最常用）
instr(string, substring [, start_position [, occurrence]])
-- 用于双字节字符环境（如中文）
instrb(string, substring [, start_position [, occurrence]])

-- 基础示例
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（从末尾开始）
    -- 从倒数第5个字符开始向左搜索
    instr('searching in this string', 'in', -5) as pos4     -- 返回: 13
from dual;

-- 查找特定次数的出现
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
    -- 结合负起始位置：从右向左找第一个'in'
    instr('in the beginning and in the end', 'in', -1, 1) as last_in   -- 返回: 20
from dual;

-- 创建测试数据
with test_data as (
    select '中国北京' as chinese_string from dual
)
select 
    -- instr：基于字符计数
    instr(chinese_string, '北京') as char_position,      -- 返回: 3
    -- instrb：基于字节计数（utf-8示例）
    instrb(chinese_string, '北京') as byte_position      -- 返回: 7（如果每个中文字符3字节）
from test_data;
-- 实际数据库中的验证
select 
    '测试字符串' as test_str,
    length('测试字符串') as char_length,      -- 返回: 5
    lengthb('测试字符串') as byte_length,     -- 返回: 15（如果utf-8，每个中文3字节）
    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. 复杂验证：检查是否符合特定模式
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日志，提取关键信息
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,
    -- 提取状态码（倒数第三个空格后的3位数字）
    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. 执行示例
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检查是否包含区号
    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通常不能有效使用索引

-- 解析嵌套结构字符串
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,
    -- 查找对应的结束位置（找到匹配的}）
    -- 这是一个简化实现，实际需要处理嵌套
    instr(json_like, '}', instr(json_like, 'user:{')) as user_end,
    -- 提取user对象内容
    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优化like查询
-- 原始查询（可能不会使用索引）
select * from products 
where product_name like '%premium%';
-- 优化版本（如果存在product_name上的函数索引）
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
        -- 检查是否包含多个关键词
        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参数
        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,
    -- 计算关键词出现次数（简化方法）
    (length(document_text) - 
     length(replace(document_text, 'urgent', ''))) / 
     length('urgent') as urgent_count
from documents;

-- 1. 为instr创建函数索引
create index idx_desc_search on products(instr(description, 'premium'));
-- 查询时使用相同的表达式
select * from products 
where instr(description, 'premium') > 0;
-- 2. 避免在where子句中对列进行函数包装（坏例子）
select * from products 
where instr(upper(description), upper('premium')) > 0;  -- 无法使用索引
-- 3. 更好的做法：存储时统一大小写或创建函数索引
create index idx_desc_upper on products(instr(upper(description), 'premium'));
-- 4. 分区表上的应用
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')
);
-- 创建本地索引
create index idx_log_msg_search on log_messages(instr(message, 'error')) local;

-- 陷阱1：空字符串的处理
select 
    instr('test', '') as empty_search,      -- 返回: 1（可能不符合预期）
    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：多字节字符的误用
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;

-- 监控数据质量问题
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;
-- 定期运行数据质量检查
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;
/

-- 实现高级搜索功能
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;