时间戳

首页 > 解决方案 > 如何从 PostgreSQL 中的文本中删除分隔部分？

sql - 如何从 PostgreSQL 中的文本中删除分隔部分？

问题描述

我想从字符串中消除一些文本模式，我的字符串有一个管道分隔符，并且参数并不总是相互跟随。

这是我的字符串

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

我想消除TType=SEND和URL=min://j?_a=3&ver=1.1

因此我的最终结果应该是

Status=OK|day=3

我已经尝试过。不在 postgresql中工作。

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

标签： sqlregexpostgresqlreplacestring-function

解决方案

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

推荐阅读