CODEWITHSUNDEEP
regexoracle-databaseregexp-replace
User0911
User0911

Reputation: 1582

Oracle regex replace issue

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?

Upvotes: 1

Views: 115

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627600

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'
)

Upvotes: 2

Related Questions