Regarding Regexp_replace - Oracle SQL

Question

I'm using the below query to replace the value 2 with 5. My input string will be in the format as shown below. Each value will be delimited with carrot(^) symbol. It's working fine when there is no duplicate value. But with duplicate values it's not working. Please advice.

select regexp_replace('1^2^2222^2','(^|\^)2(\^|$)','\15\2') OUTPUT from dual;

Output:

1^5^2222^5 ( Working Fine as there is no consecutive duplicates at the starting or at the end)

.

select regexp_replace('2^2^2222^2^2','(^|\^)2(\^|$)','\15\2') OUTPUT from dual;

Output:

5^2^^5^2222^5^2(Not working as there is consecutive duplicate at the starting/end)

Please let me know how to correct this?

What is the rule for your desired output?

Lalit Kumar B
– Lalit Kumar B

2016-03-01 08:49:27 +00:00
Commented Mar 1, 2016 at 8:49 — Lalit Kumar B
– Lalit Kumar B, Commented Mar 1, 2016 at 8:49
The ^ symbol is a caret.

MT0
– MT0

2016-03-01 09:39:47 +00:00
Commented Mar 1, 2016 at 9:39 — MT0
– MT0, Commented Mar 1, 2016 at 9:39
Thanks. Will ensure it in future

Vinoth Karthick
– Vinoth Karthick

2016-03-01 11:46:03 +00:00
Commented Mar 1, 2016 at 11:46 — Vinoth Karthick
– Vinoth Karthick, Commented Mar 1, 2016 at 11:46

Mehdi · Accepted Answer · 2016-03-01 09:30:30Z

2

problem

The problem is that the second adjacent occurrences of the searched string is not matched. This is because of the first portion of the regex:

(^|\^)2(\^|$)
  ^
-- this is not matched when the text preceding "2" is a replaced string

solution

One way to solve your problem is to run the regex twice in a row:

SELECT REGEXP_REPLACE (tmpRes, '(^|\^)2(\^|$)', '\15\2') OUTPUT
FROM (
    -- first pass of replacement
    SELECT REGEXP_REPLACE ('2^2^2222^2^2', '(^|\^)2(\^|$)', '\15\2') tmpRes
    FROM DUAL
)

-- OUTPUT: 5^5^2222^5^5

answered Mar 1, 2016 at 9:30

Mehdi

7,4431 gold badge37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:31:10Z

Why it doesn't work:

When the regular expression parses the string 2^2^2222^2^2 it will match start-of-the-string then 2^ and replace it with 5^. It will then try to continue from after that match to generate further matches - the next part of the string is 2^ however it won't match as it is not at the start of the string and there is not a leading ^.

Instead you could do it by splitting the string (using the ^ as a delimiter) into a collection and then doing the replacement on each item in the collection and re-concatenating the collection back into a single string. This could be done with a hierarchical query but implementing a simple function would make resulting query much simpler to read.

Oracle Setup:

CREATE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN VARCHAR2_TABLE DETERMINISTIC
AS
  p_result       VARCHAR2_TABLE := VARCHAR2_TABLE();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Query:

SELECT LISTAGG( CASE COLUMN_VALUE WHEN '2' THEN '5' ELSE COLUMN_VALUE END, '^' )
         WITHIN GROUP ( ORDER BY ROWNUM )
FROM   TABLE( split_String( '2^2^2222^2^2', '^' ) );

Output:

5^5^2222^5^5

Alex Poole · Accepted Answer · 2016-03-01 09:50:29Z

As others have said, the problem is the terminating delimiter caret being consumed matching the first occurrence, so it isn't seen as the opening delimiter for the next instance.

If you don't want to use nested regex calls, you could use a simple replace to double up the delimiters, then strip them afterwards:

replace(
  regexp_replace(
    replace(<value>, '^', '^^'), '(^|\^)2(\^|$)','\15\2'), '^^', '^')

The inner replace turns your value into 2^^2^^2222^^2^^2, so after the first occurrence is matched there is still a caret to act as the opening delimiter for the second instance, etc. The outer replace just strips those doubled-up delimiters back to single ones.

With some sample strings:

with t (input) as (
  select '1^2^2222^2' from dual
  union all select '2^2^2222^2^2' from dual
  union all select '2^2^2222^2^^2^2' from dual
)
select input,
  replace(
    regexp_replace(
      replace(input, '^', '^^'), '(^|\^)2(\^|$)','\15\2'), '^^', '^') as output
from t;

INPUT           OUTPUT             
--------------- --------------------
1^2^2222^2      1^5^2222^5          
2^2^2222^2^2    5^5^2222^5^5        
2^2^2222^2^^2^2 5^5^2222^5^^5^5

Collectives™ on Stack Overflow

Regarding Regexp_replace - Oracle SQL

3 Answers 3

problem

solution

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

problem

solution

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related