Column Engine replace_regexpr in calculation view

[arnold_bosserman2]

Anyone familiar with the column engine string function replace_regexpr in a calculation view? I've looked in the SAP HANA Modeling Guide 2.0 SPS 04 and it is not documented there.

Syntax: replace_regexpr( stringpatttern, stringoption, stringsource, stringreplacement, intstartpos, intoccurrence)

Most of the parameters are self explanatory. I'm trying to get more definitive information on intoccurrence. If not 1, then it sometimes has issues. Is there a value here (like 0) that replaces all occurrences?

thanks in advance!

[lbreddemann · Accepted solution]

As usual, if the function is not officially documented, it is considered to be unsupported.

My guess here is that this (column expression) function works as a build-in wrapper for the same functionality that is otherwise available via the SQL function REPLACE_REGEXPR.

If you compare the syntax, it seems to be a good match:

REPLACE_REGEXPR(<pattern> 
[ FLAG <flag> ] 
 IN <regex_subject_string> 
 [ WITH <replacement_string> ] 
 [ FROM <start_position> ] 
 [ OCCURRENCE <regex_replace_occurrence> ])

replace_regexpr( stringpatttern
, stringoption
, stringsource
, stringreplacement
, intstartpos
, intoccurrence)

A quick test with that mapping shows that this assumption is correct and one further test shows: yes, the value 0 for intoccurence indeed maps to replace ALL occurrences.

The big question now is of course: does it make a difference whether or not one uses the SQL engine or the COLUMN ENGINE in the calculated column?

Once again, a quick check via EXPLAIN PLAN and PLANVIZ reveals: in both cases, the expression is evaluated in a JECalculate plan operator. So the only real benefit I see for having this in a COLUMN ENGINE formula expression is when one wants to create combined expressions where one of the expressions is the REPLACE_REGEXPR and the other one a COLUMN ENGINE expression that otherwise would have to be combined via multiple calculated columns.

That's of course just my view into it. Since you've found this undocumented expression somewhere, why not ask your SAP HANA developer colleagues and share their insights?

Cheers,

Lars