SQL - SAP HANA - REPLACE_REGEXPR In Column Table

I have some tables on SAP HANA and „create column table“ to combine multiple „raw tables“ and need to replace strings from one column in the newly created table. Tablename

Solution 1:

@Mike, could you please try following SQLScript command

CREATE COLUMN TABLE Testsubject_status2 AS (
Select
    Table1.Person AS "Person",
    Table1.Vers AS "Vers",
    Table2.Flnr AS "Flnr",
    Table3.Status AS "Status",
    REPLACE_REGEXPR ('test with the id [[:digit:]]* is done' FLAG 'i' IN Table3.STATUS WITH 'Test is done') "replace_regexpr"
FROM 
   Table1, Table2, Table3 
WHERE Table1.Person = Table2.Person
AND Table2.Flnr = Table3.Flnr
);

This will produce a table with following sample data

Note that the STATUS column is replaced with a static text if there is a match for the given condition. Else the STATUS text is kept as it is

For the additional info, I added following expressions but I did not like it much Maybe there are better solutions

REPLACE_REGEXPR (
    '(test with the id|Deployment for the ID) [[:digit:]]* is (done|completed)' 
    FLAG 'i' 
    IN Table3.STATUS 
    WITH 
        case 
            when Table3.STATUS LIKE_REGEXPR('test') Flag 'i' then 'test is done' 
            when Table3.STATUS LIKE_REGEXPR('deploy') Flag 'i' then 'deployment is done' 
            else Table3.STATUS
        end 
)  as "replace_regexpr_ext"

You can add this add a new calculated column in your table definitions script

I assume you have following status text in your table data:

• Deployment for the ID 234 is completed
• Deploy development