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=.*?(\||$))', '')
Answer:
SELECT
REGEXP_REPLACE(
REGEXP_REPLACE('TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3',
'(TType|URL)=[^|]*\|?', '','g'),
'\|$', '');
Explanation:
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.
Then you would also need to add the global flag 'g', in order to replace all occurences of pattern, as described in the documentation.
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
SELECT
string_agg(elements,'|') -- 3
FROM mytable,
regexp_split_to_table(mystring, '\|') as elements -- 1
WHERE split_part(elements, '=', 1) = ANY(ARRAY['TType', 'URL']) IS NOT TRUE -- 2
A=B. Move every into a separate record= character and filter for elements without key = TType or URL
..from
select and from . i.e. select (the solution here) from tableThe S-Man's answer is a working one 👍
Sure upvoted, solution is okay however it does not fully satisfy my question. since i would want the solution to be within select and from
If this is a "mandatory" requirement then I see the following options:
The final query may look like:
SELECT t.*, s.result
FROM t
LEFT JOIN LATERAL (
SELECT string_agg(elements,'|') AS result
FROM regexp_split_to_table(t.col, '\|') as elements
WHERE split_part(elements, '=', 1) = ANY(ARRAY['TType', 'URL']) IS NOT TRUE) s ON TRUE
Alternatively by using subquery in SELECT list:
SELECT t.*,
(
SELECT string_agg(elements,'|') AS result
FROM regexp_split_to_table(t.col, '\|') as elements
WHERE split_part(elements, '=', 1) = ANY(ARRAY['TType', 'URL']) IS NOT TRUE
) AS result
FROM t
The following regex based solution should do the trick:
SELECT TRIM(REGEXP_REPLACE(
'TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3',
'(TType|URL)=[^|]*(\||$)', '', 'g'), '|')
-- outputs:
-- Status=OK|day=3
How the pattern works:
(TType|URL)=[^|]*(\||$)
|-----------|----|-----
1 2 3
TType or URL followed by =|The g flag is described in the documentation as
flag g specifies replacement of each matching substring rather than only the first one.
It is necessary here as we want to replace all substrings that match our pattern.
Finally, sometimes a single | character might remain at the end of the string. Any trailing | character is trimmed from the result using TRIM
(Status|TType|day)=[^|]*(\||$) i have tried and it is ok. is it possible to remove the pipe at the end from above regex. this is the result URL=min://j?_a=3&ver=1.1|
TRIM function, the trailing | character can be removed.There were a few issues with your attempted regular expression:
.*? match was used, this could still include pipe symbols. This can be rectified by using a matcher that allows anything except a pipe symbol (this can be greedy): [^|]*'g' flag to replace all occurrences, not just the first.URL=... in your example).From addressing the points above, here is a working version:
SELECT REGEXP_REPLACE('TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3', '((Status|TType)=[^|]*[|]|[|](Status|TType)=[^|]*)', '', 'g')
Rextester demo: https://rextester.com/CYBP40923
(Status|TType|day)=[^|]*([|]|$) why is it that this leaves a pipe in the end of the result.
((Status|TType|day)=[^|]*[|]|[|](Status|TType|day)=[^|]*). (Have now edited my answer accordingly).