PostgreSQL: built-in function to remove substring starting with certain pattern

Question

In my table I have a col_A like below:

col_A
-----------------------------------
Dog_qq123r_sdf
Cat_old_qq34569er
Bird_tweet_A_qqikdf0_qq_sdfad
Cow_moo_moo_qq1_qq2_qq3

I would like to create a derived column derived_col_A from col_A. The rule is 'remove everything starting from first _qq'

col_A                            derived_col_A
------------------------------------------------
Dog_qq123r_sdf                      Dog
Cat_old_qq34569er                   Cat_old
Bird_tweet_A_qqikdf0_qq_sdfad       Bird_tweet_A
Cow_moo_moo_qq1_qq2_qq3             Cow_moo_moo

Is there a built-in function that can transform col_A to derived_col_A?

Answer 1

You could use regexp_replace() to handle this:

 SELECT regexp_replace(Col_A, '_qq.*$',NULL) from your_table;

That will replace everything after (and including) _qq with null.