SQL-SAP HANA-列表中的REPLACE_REGEXPR [英] SQL - SAP HANA - REPLACE_REGEXPR in Column table

问题描述

我在SAP HANA上有一些表和创建列表"来组合多个原始表"，并且需要替换新创建表中一列的字符串.表名"Testsubject_status"列名"STATUS". 我需要替换字符串的原因是为了使特定条目的措辞统一. 通过以下示例，希望可以更清楚地说明我的意思:

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:

表名称:Testsubject_status ---列:状态

Table name: Testsubject_status --- Column: Status

• 测试我
• 测试他
• ID为1237的测试已完成
• 测试她
• 已完成ID 928162的测试
• ID 991的测试已完成

结果应为

表名称:Testsubject_status ---列:状态

Table name: Testsubject_status --- Column: Status

• 测试我
• 测试他
• 测试完成
• 测试她
• 测试完成
• 测试完成

我尝试了以下操作:

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
);

创建表正在工作. Replace_Regexpr语句仅在不与create column table语句一起运行时才起作用，然后仅创建一个包含一列的表，并且每行中都有条目"TEST with％".

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.

其他信息:

• 不仅需要协调测试完成"字符串，而且还有一些其他字符串需要协调.因此，我需要在此特定列状态"中多次使用replace语句
• 测试已完成"语句与表中的另一条语句不是1:1的关系，因此不能以其他任何方式使用其他语句:-)

不确定以这种方式创建表格是否是最好的表格，但我想那是另一回事了

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

提前感谢您的输入！

此图片用于在注释中进行说明:

This Picture is for clarification in the comments:

推荐答案

@Mike，能否请您尝试执行以下SQLScript命令

@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

请注意，如果给定条件匹配，则STATUS列将替换为静态文本.否则，STATUS文本将保持不变

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:

• ID 234的部署已完成
• 部署开发

这篇关于SQL-SAP HANA-列表中的REPLACE_REGEXPR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！