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