I can find a word pretty easily with a pattern like (^|\W)whatever($|\W). But if I use that pattern to replace a word, I also replace the non-word characters that correspond to \W.
In the case below I lose the space before "this" as well as the question mark after it. Is there a better way to swap out a single word in a line of text?
select regexp_replace('What is this?', '(^|\W)this($|\W)','that') replaced
from dual;
Note that I only want whole words. If I'm replacing "this" with "that", I do not want to match "thistle".
My larger project is address normalization, e.g. Boulevard --> Blvd, East --> E, etc.
Thanks all.