Search code examples
sqlpostgresqlregexp-replace

Postgres Query to replace the last occurence of input data

Input:

field
ABC_PKG_T
ABC_T_PKG
ABC_PKG_T2
abc_base_t

Output:

field
ABC_PKG
ABC_T_PKG
ABC_PKG_T2
abc_base

I have used the following logic:

REGEXP_REPLACE(TRIM(field), '(.*)\_T|_t', '\1' )

and it's working fine to fetch most of the output value except for the third output value I am getting "ABC_PKG2".

Can you please suggest any other logic so that I will get the result as ABC_PKG_T2 correctly?

Solution

  • You need to anchor the expression at the end of the string using $:

    regexp_replace(the_column, '_t\s*$', '', 'i')

    The \s* will also remove any trailing white space that follow the _t and the 'i' makes this case insensitive so it will remove _T as well as _t