0

What are the exact possibilities for Calculated Columns in SAP HANA?

I have often trouble building calculated columns - even though using SQL (not column engine)

So I have to use concat(col1, col2) instead of col1 || col2

Now I want to do:

the equivalent of ABAP CONDENSE( COL1 )

HANA SQL : replace_regexpr ( '[[:space:]]+' IN COL1 WITH ' ')

How to do it in a calculated column?

Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113
  • I don't use HANA, but I believe you need to include `OCCURRENCE ALL` after your `WITH ' '` in that `replace_regexpr()` formula to insure that all occurences of `[[:space:]]+` are matched. And that `COL1` you may want to wrap in `TRIM()` to knock off the leading and ending whitespace before `replace_regexpr()` gets it's hand on it. – JNevill May 03 '17 at 13:00

1 Answers1

2

It seems that the graphical modeller supports only the functions listed in the Function window of the Expression Editor. However, if you create a scripted calculation view you will be able to active the following code:

/********* Begin Procedure Script ************/ 
BEGIN 
   var_out = select REPLACE_REGEXPR('[[:space:]]+' in "<YOUR_COLUMN>" with ' ') as "CONDENSED_TEXT" from "<YOUR_SCHEMA>"."<YOUR_TABLE>";

END /********* End Procedure Script ************/

The use of Table Functions in Calculation views could be another possibility.

rene
  • 1,618
  • 21
  • 26