Error with brackets within Oracle REGEXP_REPLACE function

Question

I've written an Oracle function, which replace some symbols to other. Example of string:

SELECT ReplaceStringFormat('Employee with FIO <FIO>') FROM dual;

Result after replace:

'Employee with FIO '||FIO||''

And I have an error ORA-12725 when string begins with bracket or end with bracket like this:

SELECT ReplaceStringFormat('(<DEPTID>) have <EMP_NUMB> employees') FROM dual;

or

SELECT ReplaceStringFormat('Employee <FIO> works in dept (<DEPTID>)') FROM dual;

I'm a newbie in REGEXP.. Please, explain me, how can I solve my problem.

Function's code:

CREATE OR REPLACE FUNCTION ReplaceStringFormat (p_source_string IN VARCHAR2)
   RETURN VARCHAR2 IS

 v_result_string VARCHAR2(4000);
  v_counter BINARY_INTEGER := 1;
  v_flag NUMBER(1);
  v_last_char CHAR(1);
  v_last_char_new VARCHAR(5);
  v_first_char CHAR(1);
  v_first_char_new VARCHAR(5);
  v_first_char_flag NUMBER(1) := 0;

  BEGIN
    v_result_string := p_source_string;
    v_flag := 0; 
     WHILE v_counter <= 2 LOOP
      IF v_flag = 0 THEN
       IF INSTR (v_result_string, '<') = 1 THEN
        v_result_string := REGEXP_REPLACE(v_result_string, '<', '', '1', '1');
        v_first_char_flag := 1;
       ELSE
        IF v_first_char_flag = 0 THEN 
         v_first_char := SUBSTR(v_result_string, 1, 1);
         v_first_char_new := ''''||v_first_char;
         v_result_string := REGEXP_REPLACE(v_result_string, v_first_char, v_first_char_new, 1);
         v_first_char_flag := 1;
        ELSE
         v_result_string := REPLACE(v_result_string, '<', '''||');
         v_counter := v_counter + 1;
         v_flag := 1;
        END IF;
      END IF;
      ELSE
       v_result_string := REPLACE(v_result_string, '>','||''');
       v_last_char := SUBSTR(v_result_string, LENGTH(v_result_string), 1);
       v_last_char_new := v_last_char||'''';
       v_result_string := REGEXP_REPLACE(v_result_string, v_last_char, v_last_char_new, LENGTH(v_result_string));
       v_counter := v_counter + 1;
     END IF;
    END LOOP;  
  RETURN v_result_string;
END ReplaceStringFormat;

ORA-12725 appears at lines:

v_result_string := REGEXP_REPLACE(v_result_string, v_first_char, v_first_char_new, 1);

and

v_result_string := REGEXP_REPLACE(v_result_string, v_last_char, v_last_char_new, LENGTH(v_result_string));

Thanks for advice!

Why do you need to write your function when you already have built in functions in Oracle? What is the desired output? What is the purpose of the function you wrote? Didn't you already get your answers here: stackoverflow.com/questions/16054892/… — Art
– Art, Commented Apr 17, 2013 at 14:20
Reading between the lines, are <FIO> and <DEPTID> placeholders for values that need to be substituted into the strings? In this case a simple select replace('Employee <FIO> works in dept (<DEPTID>)','<FIO>', fio_column) from table_containing_fio_column would be all that is required. As many nested calls to replace are needed as you have placeholders. — Colin 't Hart
– Colin 't Hart, Commented Apr 17, 2013 at 14:48

Vincent Malgrat · Accepted Answer · 2013-04-17 14:38:58Z

I'm not sure you really need regular expressions here. Standard REPLACE should work:

SQL> SELECT '''' || REPLACE(REPLACE(txt, '<', '''||'), '>', '||''') || '''' rep
  2    FROM (SELECT 'Employee <FIO> works in dept (<DEPTID>)' txt FROM dual);

REP
-------------------------------------------------
'Employee '||FIO||' works in dept ('||DEPTID||')'

Regarding your ORA-12725 error, you should add debug information in your code. I'm not sure what you are trying to accomplish here, but if you add an exception block you will find:

v_result_string = 'Employee '||FIO||' works in dept ('||D'EPTID||')
v_last_char     = )
v_last_char_new = )'

Here the string ) is not a correct regular expression.

It seems you want to replace the last character of the string by this character followed by a single quote. Once again I think you're trying to use regular expressions when they are not the most appropriate tool. Appending a single quote is better left to the append operator || (or the concat function).

Regular expressions are a powerful tool but they are not the best-suited for every tasks. To do basic things like appending strings you should rather use standard functions.

Collectives™ on Stack Overflow

Error with brackets within Oracle REGEXP_REPLACE function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related