CODEWITHSUNDEEP
sqloracle-database
Don Burton
Don Burton

Reputation: 1

Using Regexp_Replace with spaces

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.

Upvotes: 0

Views: 561

Answers (2)

MT0
MT0

Reputation: 168041

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;

Upvotes: 1

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

Reputation: 50017

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.

Upvotes: 0

Related Questions