Using Regexp_Replace with spaces

Question

I have an expression like so:

Select regexp_replace('Seattle, WA Se', 'Se', 'SE')

I want to replace the 'Se' with 'SE' but don't want to touch the word 'Seattle'. So far, both 'Seattle' and 'Se' get changed to 'SEattle' and 'SE'.

How do I add a condition in the RegExp_Replace expression to find and change on the "stand-alone" 'Se'?

Thanks.

MT0 · Accepted Answer · 2016-12-21 01:06:32Z

1

How do I add a condition in the RegExp_Replace expression to find and change on the "stand-alone" 'Se'?

Search for a preceding space character (or start-of-the-string) then Se then a following space character (or end-of-the-string) and use back references to maintain the preceding and following space characters in the replacement string:

SELECT REGEXP_REPLACE(
         'Seattle, WA Se',
         '(^|\s)Se(\s|$)',
         '\1SE\2'
       )
FROM DUAL;

answered Dec 21, 2016 at 1:06

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

Patrick Bacon Over a year ago

When I first looked at your answer, I thought, why are you not using word anchors? I did not know that Oracle database does not include this in its reg expression engine.

MT0 Over a year ago

@PatrickBacon If it did then it would make life so much easier - especially if trying to match Se Se Se - which this answer has issues with (but can be fixed by, first, inserting a repeat of each white space character then, secondly, doing the above replacement and then, finally, removing each repeated white space; however, that's a lot of work which could just easily be solved by the zero-width word-boundary match).

Bob Jarvis - Слава Україні · Accepted Answer · 2016-12-21 00:04:15Z

0

Change your regular expression to

'Se$'

$ binds to the end-of-string, so 'Se$' means that 'Se' must appear at the end of the string with no characters after it. This will let the trailing Se be matched, but the Se in Seattle won't be.

Best of luck.

edited Dec 21, 2016 at 0:04

answered Dec 20, 2016 at 23:55

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

Using Regexp_Replace with spaces

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related