Concatenate the column values based on specific patterns in the column in Postgres

rshar Published at Dev

rshar

I have following columns in Postgres table

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

I want to concatenate extracted and generated strings (p.Q296, Q297, ins) from 'symbol' and 'seq' if the seq starts with 'Q' and symbol has a pattern matching the value in column. Subsequently, drop the rows where the above pattern is missing (not able to figure out how to implement that).

The below query is unable to catch the pattern and not concatenating the strings.

The desired output is:

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

I am using following query for this.


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;

Explanation of regexp:

'(^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'

Thanks

Hambone

Try this:

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+\]$'

The "drop rows" can simply be invoked by putting your conditions in the where clause.

I ran it against your sample data and got this:

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

Collected from the Internet

Please contact [email protected] to delete if infringement.