How to create a table as a copy from another table and replace values from columns in one statement

Question

I have a table Test with a column "filename". Now I want to make a copy of that table and automatically replace the names in that column.

I search for something like:

CREATE TABLE TEST2  AS (SELECT * FROM TEST t WHERE t.filename LIKE 'file%' REPLACE(t.filename,'file/view','etc/tmp' ));

Is it possible to do this in one statement?

Answer 1

Put the replace function on the correct place.

CREATE TABLE TEST2 AS (
    SELECT
            REPLACE (
                    t.filename,
                    'file/view',
                    'etc/tmp'
            ) AS filename,
            t.otherfield,
      t.whateverfield
    FROM
        TEST t
    WHERE
      t.somefield = "xy"
);