how to escape characters like '[', ']', '|' in regexp_substr oracle

Question

i need to replace somthing like this:

some random characters[[some_randome_characters1|some_randome_characters2]]some random characters

to this:

[link=some_randome_characters]some_randome_characters[/link]

i manage to write procedure having this:

l_tmp:=regexp_substr(content,'/[{2}(/s|/w|/d)+/|/[{2}(/s|/w|/d)+/]{2}');

... here i am doing rest stuff to build my link. Problem is that (/s|/w|/d)+ dont find all matches -> for exapmle text having '.', or '=', and others. All in all i want to change this:

(/s|/w|/d)+

to this:

[^/|/[/]] 

But it wont work. Is there any way to escape those 3 characters?

Solution 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE FUNCTION extractFirstLink(
  str IN VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC
IS
  m1 VARCHAR2(4000) := REGEXP_SUBSTR( str, '\[\[(.*?)(\|(.*?))?\]\]', 1, 1, NULL, 1 );
  m2 VARCHAR2(4000) := REGEXP_SUBSTR( str, '\[\[(.*?)(\|(.*?))?\]\]', 1, 1, NULL, 3 );
BEGIN
  RETURN '[link=' || m1 || ']' || NVL( m2, m1 ) || '[/link]';
END extractFirstLink;
/

Query 1:

WITH data AS (
 SELECT 'some random characters[[some_randome_characters1|some_randome_characters2]]some random characters' AS text
 FROM DUAL
)
SELECT extractFirstLink( text )
FROM data

Results:

|                                         EXTRACTFIRSTLINK(TEXT) |
|----------------------------------------------------------------|
| [link=some_randome_characters1]some_randome_characters2[/link] |

Solution 2

You are using the shlash / character, but you must use the backslash \ to escape a character.