Oracle regex replace issue

Question

I have one oracle table with one of the columns having below values,

,21A,22,21,28,28

I want to do a regex replace for removal of ,21 (The exact match) but it removes the value from ,21A

Below is my query,

update STUDENT set SCORES = REGEXP_REPLACE(SCORE, ',21' , '', 1, 1) where STUDENT_ID = 1;

How can this be achieved? Any ideas?

My bad apparantly oracle regex has no lookarounds. Can't you just check for ,21, and replace with , instead of empty string then? — Alexander Derck
– Alexander Derck, Commented Feb 18, 2016 at 22:45
Can you fix the database design instead and save yourself other hassles down the road? This column violates 1st normal form. If you are stuck with someone else's bad design I feel your pain. — Gary_W
– Gary_W, Commented Feb 18, 2016 at 23:00
@Gary_W, This is already implemented and running. Design change will trigger lot of other changes at this time. — User0911
– User0911, Commented Feb 18, 2016 at 23:06
Maybe REGEXP_REPLACE(SCORE, ',21(,|$)' , '\1', 1, 1) is enough? Or even REGEXP_REPLACE(SCORE, ',21(\W|$)' , '\1', 1, 1)? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Feb 18, 2016 at 23:22

Wiktor Stribiżew · Accepted Answer · 2016-02-18 23:29:05Z

2

In your case, you can use a ,21(\W|$) regex. It matches ,21 that is followed with a non-word character or the end of string.

REGEXP_REPLACE(SCORE, ',21(\W|$)' , '\1', 1, 1)

Here is how you can perform a whole word search and replace in Oracle SQL:

regexp_replace( 
    haystack
    , '(\W|^)(' || what_to_replace || ')(\W|$)'
    , '\1' || with_what || '\3'
    , 1
    , 0
    , 'imn'
)

answered Feb 18, 2016 at 23:29

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

1 Answer 1