CODEWITHSUNDEEP
regexselectplsqloracle12cregexp-replace
Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

Oracle regex_replace is not excluding certain values

I have a select statement with regex_replace.

 SELECT REGEXP_REPLACE((REGEXP_REPLACE(nvl(d.text_i, d.text),'[^=|]+(\||$)')),'^'||24466||'=|\|'
                                    ||24466||'=', '||'||24466||'='||'C1')  
  FROM table_infor d
   where d.tab_id = 13095;

I values of the column text_i and text are as below

text                                            text_i
26736=A||||24470=B1||||24467=||||24466=V1       (null)
26736,24470,24467,24466                         (null)
26736=A||||24470=||||24467=||||24466=V1         (null)
26736=||||24470=B1||||24467=B||||24466=V1       (null)
(null)                                           A
123                                             (null)

So I wanted this to print :

26736=||||||24470=||||||24467=|||||||24466=C1
2673,24470,24467,24466
26736=||||24470=||||24467=||||24466=C1        
26736=||||24470=||||24467=||||24466=C1 
A
123

But what i am getting currently is

26736=||||||24470=||||||24467=|||||||24466=C1
(null)
26736=||||24470=||||24467=||||24466=C1        
26736=||||24470=||||24467=||||24466=C1

So my regexp_replace logic for all the values that has =||||| works but for the other values it is not selecting them. I want the other values to be selected as it is.

Upvotes: 1

Views: 148

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627292

The '[^=|]+(\||$)' pattern matches any one or more chars other than a = and | up to a | or end of string, consuming the pipe if it is present. That means, the match will start as soon as possible. You want the matches to start only when a = symbol occurs.

Thus, include it in the first regex and replace with = to put it back:

REGEXP_REPLACE(nvl(d.text_i, d.text),'=[^=|]+(\||$)', '=')

See this regex demo.

Upvotes: 1

Related Questions