0

In postgresql I would like to substitute just in full words and not substrings. I noticed that replace and translate replace strings even in substrings. Then, I used regexp_replace to add the following:

SELECT REGEXP_REPLACE (UPPER('BIG CATDOG'), '(^|[^a-z0-9])' || UPPER('CAT') || '($|[^a-z0-9])', '\1' || UPPER('GATO') || '\2','g')

In the previous sample, CAT should not been replaced because it is not a whole word, but a substring which is part of a word. How can I achieve to avoid the replacement? The output should be BIG CATDOG because no substitution was possible.

Thanks

Improve this question

1 Answer 1

Reset to default
2

The replacement happens because you are only checking for [^a-z0-9] after the search term, and D is not in that character class. You can resolve this by either adding A-Z to your character class:

SELECT REGEXP_REPLACE (UPPER('BIG CATDOG'), '(^|[^a-zA-Z0-9])' || UPPER('CAT') || '($|[^a-zA-Z0-9])', '\1' || UPPER('GATO') || '\2','g')

Or by adding the i flag to the replace call:

SELECT REGEXP_REPLACE (UPPER('BIG CATDOG'), '(^|[^a-z0-9])' || UPPER('CAT') || '($|[^a-z0-9])', '\1' || UPPER('GATO') || '\2','gi')

In either case you will get the desired BIG CATDOG output.

However a better solution is to use the word boundary constraints \m (beginning of word) and \M (end of word):

SELECT REGEXP_REPLACE (UPPER('BIG CATDOG'), '\m' || UPPER('CAT') || '\M', UPPER('GATO'),'g')

Demo on dbfiddle

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

1 Comment

@JuanPerez no worries. I made an edit just as you responded, using the word boundary constraints is probably a better solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.