sqlregexpostgresqlreplacestring-function

How to remove delimited sections from text in PostgreSQL?

I want to eliminate some text pattern from a string, my string has a pipe delimiter, and the parameters do not always follow each other.

This is my string

TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3

I want to eliminate TType=SEND and URL=min://j?_a=3&ver=1.1

Therefore my final result should be

Status=OK|day=3

What i have tried. Not working in postgresql .

select REGEXP_REPLACE('TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3', 
'(TType=.*?(\||$))|(URL=.*?(\||$))', '')
Solution

  • Answer:

    SELECT 
REGEXP_REPLACE(
 REGEXP_REPLACE('TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3',
  '(TType|URL)=[^|]*\|?', '','g'),
'\|$', '');

    Explanation:

    1. The .*? part in your pattern, although not greedy, consumes colons as well, so doesn't behave as intended. This is fixed by [^|]* that consumes any non colon character, zero or more times.

    2. Then you would also need to add the global flag 'g', in order to replace all occurences of pattern, as described in the documentation.

    3. Finally, in case a parameter you need to eliminate occurs last (since the parameters can appear in any order), you need to add an extra replacement step to eliminate a residual colon at the end of the string.

    For example without the extra step, the following

    SELECT
REGEXP_REPLACE('Status=OK|URL=min://j?_a=3&ver=1.1|day=3|TType=SEND',
  '(TType|URL)=[^|]*\|?', '','g');

    produces

    Status=OK|day=3|

    while, addding the extra step, the following

    SELECT 
REGEXP_REPLACE(
 REGEXP_REPLACE('Status=OK|URL=min://j?_a=3&ver=1.1|day=3|TType=SEND',
  '(TType|URL)=[^|]*\|?', '','g'),
'\|$', '');

    produces the desired

    Status=OK|day=3