Problem Statement #

We need to combine a base path with multiple tag entries to form full, bracketed paths:

• Base path: /home/pc/test
• Input tags: [/alseko][/logs][/archive]
• Expected result: [/home/pc/test/alseko][/home/pc/test/logs][/home/pc/test/archive]

Current Attempt #

Your existing code only processes a single tag and doesn’t handle all entries in the input string:

select '[' || '/home/pc/test' || ltrim(substr('[/alseko][/logs][/archive]', instr('[/alseko][/logs][/archive]', '['), instr('[/alseko][/logs][/archive]', ']')),'[') from dual

Correct Implementation #

Use Oracle’s REGEXP_REPLACE function to replace every tag pattern with the full path in one step. This automatically handles all tags in the input:

SELECT REGEXP_REPLACE(
    '[/alseko][/logs][/archive]',
    '\[\/([^\]]+)\]',
    '[' || '/home/pc/test' || '/\1]'
) AS concatenated_paths
FROM dual;

How It Works #

1. Regex Pattern: \[\/([^\]]+)\]
   • \[ matches the opening bracket [
   • \/ matches the leading slash / in each tag
   • ([^\]]+) captures the tag name (like alseko) by matching any characters that aren’t a closing bracket ]
   • \] matches the closing bracket ]
2. Replacement String: [' || '/home/pc/test' || '/\1]'
   • [ adds the opening bracket for the full path
   • '/home/pc/test/' appends your base path plus a trailing slash
   • \1 inserts the captured tag name from the regex
   • ] adds the closing bracket for the full path

Running this query will output exactly the concatenated paths you’re looking for.

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