如何使用INSTR()函数截取字符串？提取方括号内指定内容

Looks like your current query is almost there, but you’re hitting a common gotcha with Oracle’s SUBSTR function—the third parameter is the length of the substring, not the ending position. That’s why your code isn’t returning just WINDOM.

Here’s the corrected query that will give you exactly the value inside the second [] pair:

SELECT SUBSTR(
    '[TextValue][WINDOM][Camry]',
    INSTR('[TextValue][WINDOM][Camry]', '[', 1, 2) + 1,  -- Start right after the second '['
    INSTR('[TextValue][WINDOM][Camry]', ']', 1, 2) - INSTR('[TextValue][WINDOM][Camry]', '[', 1, 2) - 1  -- Calculate length to exclude brackets
) AS extracted_value
FROM dual;

Let’s break this down step by step: #

• INSTR('[TextValue][WINDOM][Camry]', '[', 1, 2): Finds the position of the second opening bracket [.
• Add 1: Shifts our starting point past the opening bracket so we don’t include it in the result.
• Calculate the length: Subtract the position of the second [ from the position of the second ], then subtract 1 more to exclude the closing bracket. This gives us exactly the number of characters between the two brackets.

For reusability (e.g., working with a column instead of a hardcoded string), replace the literal with your column name:

SELECT SUBSTR(
    your_column_name,
    INSTR(your_column_name, '[', 1, 2) + 1,
    INSTR(your_column_name, ']', 1, 2) - INSTR(your_column_name, '[', 1, 2) - 1
) AS extracted_value
FROM your_table;

Alternative: Regex for Cleaner Pattern Matching #

If you prefer a more flexible approach, Oracle’s REGEXP_SUBSTR can directly target the text inside the second bracket pair:

SELECT REGEXP_SUBSTR('[TextValue][WINDOM][Camry]', '\[([^\]]+)\]', 1, 2, NULL, 1) AS extracted_value
FROM dual;

Regex breakdown:

• \[: Escaped opening bracket (since [ is a special regex character)
• ([^\]]+): Captures one or more characters that aren’t a closing bracket (this is our target value)
• \]: Escaped closing bracket
• 1, 2: Start at position 1, match the 2nd occurrence of the pattern
• 1: Return the first captured group (the text inside the brackets)

Either method will return your desired result: WINDOM.

内容的提问来源于stack exchange，提问作者RealMan