I have a table like the following in Greenplum

I want to append CLASS_5 string to each of the name in this table. If name already has CLASS_4 or CLASS_3 it should be replaced with CLASS_5 using select query. We can append string in select query like below.

select Name||'CLASS_5', Marks from student

How to replace already existing "CLASS_X" ? I want to have end result table as below.

CodePudding user response：

You may try the following:

SELECT CASE WHEN Name LIKE'%CLASS%' THEN
            REGEXP_REPLACE(Name,'CLASS_\d','CLASS_5')
            ELSE Name || '_CLASS_5'
       END AS Name,
       Marks
FROM student;

See a demo on Postgres.