REGEXP_REPLACE replace spaces between two symbols

One (inefficient) way of doing this is by doing multiple REGEXP_REPLACE calls.

For example, lets look at the following plpgsql function.

CREATE OR REPLACE FUNCTION replaceSpacesBetweenTwoSymbols(startChar TEXT, endChar TEXT, textToParse TEXT)
    RETURNS TEXT
AS $$
DECLARE resultText TEXT := textToParse;
DECLARE tempText TEXT := textToParse;
BEGIN
    WHILE TRUE LOOP
        tempText = REGEXP_REPLACE(resultText,
                                  '(' || startChar || '[^' || endChar || ']*)' || '( )(.*' || endChar || ')',
                                  '\1%\3');
        IF tempText = resultText
        THEN RETURN resultText;
        END IF;
        resultText := tempText;
    END LOOP;
    RETURN resultText;

END;
$$
LANGUAGE 'plpgsql';

We create a function that takes three arguments, the startChar, the endChar and the textToParse which holds the text that will be trimmed.

We start by creating a a regular expression based on the startChar and endChar. If the value of startChar is @ and the value of endChar is & we will get the following regular expression:

(@[^&]*)( )(.*&)

This regular expression is consisted of three groups:

1. (@[^&]*) - This group matches the text that is between the @ and an an empty space character - ' ';

2. ( ) - This group matches a single space character.

3. (.*&) - This group matches the text that is between a space character and the & character.

In order to replace the space (group 2), we use the following REGEXP_REPLACE call:

REGEXP_REPLACE(resultText,' (@[^&]*)( )(.*&)', '\1%\3')

From that expression you can see that we are replacing the second group (which is a space) with the % character.

This way, we will only replace one space per one REGEXP_REPLACE execution. Once we find that there are no more spaces that need to be replaced, we return the modified TEXT.

At this moment, the spaces are replaced with % characters. One last thing we need to do is to replace the multiple consecutive % characters with a single %.

That can be done with another REGEXP_REPLACE call at the end. So for example:

SELECT REGEXP_REPLACE(replaceSpacesBetweenTwoSymbols('@','&','this @ is test   &that did not @turn& out well'),'%{2,}','%');

Will return

this @%is%test%&that did not @turn& out well

as a result, while this

SELECT REGEXP_REPLACE(replaceSpacesBetweenTwoSymbols('@','&','this is @a more  complex& task @test a a & w'),'%{2,}','%');

will return

this is @a%more%complex& task @test%a%a%& w

as a result.