regex - 根据 Postgres 中列中的特定模式连接列值

问题描述

我在 Postgres 表中有以下列

row_id  seq         symbol
37697   QQQQQQQQQQ  p.Q296[65]
37698   QQQQQQQ     p.Q296[65]
37760   HNHH        p.Q296[65]
37761   HNHH        p.Q296[65]
47791   QQQ         p.Q296[65]
47792   QQQ         p.Q296[65]
47793   HNHH        p.Q296[65]
47794   HNHH        p.Q296[65]
47795   NHRFCDS     p.Q296

如果seq以'Q'开头并且symbol具有与列中的值匹配的模式，我想连接从'symbol'和'seq'提取和生成的字符串（p.Q296，Q297，ins）。随后，删除缺少上述模式的行（无法弄清楚如何实现）。

以下查询无法捕获模式并且无法连接字符串。

所需的输出是：

row_id  seq         new_col
37697   QQQQQQQQQQ  p.Q296_Q297insQQQQQQQQQQ
37698   QQQQQQQ     p.Q296_Q297insQQQQQQQ
47791   QQQ         p.Q296_Q297insQQQ
47792   QQQ         p.Q296_Q297insQQQ
47795   NHRFCDS     p.Q296

我为此使用以下查询。


SELECT              
   row_id,
   seq,                 

           CASE 

                WHEN symbol ~ '(^p..\d+)\[\d+]$'  AND  seq ~ '^Q.*')   
                THEN (substring(symbol, '(^p..\d+)\[\d+]$')) || '_' || ((substring(symbol, '^p..(\d+)\[\d+]$'))::INT)+1 ||  'ins' || seq

                WHEN symbol ~ '(^p..\d+)\[\d+]$'  AND  seq !~ '^Q.*')   
                THEN DROP ....

                ELSE symbol

                END AS new_col



                FROM table;

正则表达式的解释：

'(^p..\d+)\[\d+]$') --> extract  p.Q296
_  --> used for concatenation
^p..(\d+)\[\d+]$'))::INT)+1  --> extract the value after Q and add 1 to it
ins --> concatenate string 'ins'
seq --> concatenate value from column 'seq'

谢谢

标签： regexpostgresql

试试这个：

select
  row_id, seq,
  case
    when seq like 'Q%' and symbol ~ '^p..\d+\[\d+\]$' then
      concat(
        regexp_replace(symbol, '\[.+', ''),
        '_',
        substring (symbol, 3, 1),
        cast (substring (symbol from 'p..(\d+)') as integer) + 1,
        'ins',
        seq
      )
    else symbol
  end as new_col
from foo
where
  seq like 'Q%' or symbol !~ '^p..\d+\[\d+\]$'

可以通过将条件放在 where 子句中来简单地调用“删除行”。

我针对您的示例数据运行它并得到了这个：

37697   QQQQQQQQQQ  p.Q296_Q297insQQQQQQQQQQ
37698   QQQQQQQ     p.Q296_Q297insQQQQQQQ
47791   QQQ         p.Q296_Q297insQQQ
47792   QQQ         p.Q296_Q297insQQQ
47795   NHRFCDS     p.Q296

regex - 根据 Postgres 中列中的特定模式连接列值

问题描述

解决方案

推荐阅读