Agfdhyk

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 “Testsubject_status” Column name “STATUS”.
The reason why I need to replace the strings, is to get a harmonized wording on specific entries.
With the following example, it is hopefully more clear what I mean:

Table name: Testsubject_status --- Column: Status

• Test me


• Test him


• Test with the ID 1237 is done


• Test her


• Test with the ID 928162 is done


• Test with the ID 991 is done

The result should be

Table name: Testsubject_status --- Column: Status

• Test me


• Test him


• Test is done


• Test her


• Test is done


• Test is done

I tried the following:

CREATE COLUMN TABLE SCHEMATTT.Testsubject_status AS (

    Select

        Table1.Person AS “Person”,

        Table1.Vers AS “Vers”,

        Table2.Flnr AS “Flnr”,

        Table3.Status AS “Status”

FROM 

    SCHEMATTT.Table1, SCHEMATTT.Table2, SCHEMATTT.Table3 

WHERE SCHEMATTT.Table1.Person = SCHEMATTT.Table2.Person

AND SCHEMATTT.Table2.Flnr = SCHEMATTT.Table3.Flnr





SELECT

REPLACE_REGEXPR (‘with the id d{1,}’ IN ‘TEST with %’ WITH ‘’) “replace_regexpr”

FROM SCHEMATTT.Testsubject_status

);

Creating the table is working. The Replace_Regexpr statement is only working if I do not run it together with the create column table statement and then only creates a table with one column and the entries ‘TEST with %’ in every row.

Additional info:

• There is not only the “Test is done” string that needs to be harmonized, but a few others as well. So I need to use the replace statement more than once in this specific column “Status”


• The "Test is done" Statement is not 1:1 with another Statement in the table, so the other statements can't be used in any way to do this :-)

Not sure if creating the table in this way is the best one, but I guess that’s another story 

Thank you in advance for your input!

This Picture is for clarification in the comments:

@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