sqlgreenplum

Appending postfix string to result of select query in Greenplum database


I have a table like the following in Greenplum

Name Marks
George_Henry_CLASS_3 94
Jhon 72
Michael 59
David_Warner_CLASS_4 88

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.

Name Marks
George_Henry_CLASS_5 94
Jhon_CLASS_5 72
Michael_CLASS_5 59
David_Warner_CLASS_5 88

Solution

  • 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.