SAP HANA Long Text to split to multiple Columns

[Niroop]

Hello Experts,

I have a scenario where i need to split long text to multiple columns with respective field in Graphical Calculation views.

Ex: M-68727|Enery Fab| INDIA|Global Products(This long text is coming from STXL table).

Now i need to split like below columns

Material Name Land Business Unit

M-68727 Enery Fab INDIA Global Products

Can anyone please help to achieve this .

Thanks,

Nischal.

[Abhishek_Hazra · Accepted solution]

Hi,

You can do that by creating 2 more calculated columns (in SQL engine). Using the string functions SUBSTR_BEFORE & SUBSTR_AFTER with delimiter '|', you can derive all your desired columns from the long text as calculated columns.

Follow the steps as below : (all calculated columns should be using SQL engine & can be created in same node)
1. Create calculated column : let's say : TEXT2

SUBSTR_AFTER("STXL_FIELD",'|')

2. Create calculated column : let's say : TEXT3

SUBSTR_AFTER("TEXT2",'|')

3. Now you can create all your desired calculated columns in the same node as below :

MATERIAL :

SUBSTR_BEFORE("STXL_FIELD",'|')

NAME :

SUBSTR_BEFORE("TEXT2",'|')

LAND :

SUBSTR_BEFORE("TEXT3",'|')

BUSINESS_UNIT :

SUBSTR_AFTER("TEXT3",'|')

This will give you the desired output completely graphical way. Hope this helps 🙂

Best Regards,

Abhi

[Abhishek_Hazra · Accepted solution]

hi nischal.m ,

Has the suggestion worked out for you? Please do let us know if you still have concerns. Else, please consider closing the thread accepting the answer if that worked out. 🙂

Best Regards,
Abhi

[Niroop · Accepted solution]

Hi Abhi,

Thanks for your Inputs.

Actually i cant see SUBSTR_AFTER in String Functions.i have below functions only.

So i am getting desired out put by below function.

Kindly let me know for any further inputs.

Thanks,

Niroop.

[Niroop · Accepted solution]

Hello Abhi,

I have tuned my code like below and now its working.

So in text Column i am using above text

Thanks for your inputs.

Thanks,

Niroop.

[Abhishek_Hazra · Accepted solution]

Hello nischal.m, SUBSTR_BEFORE & SUBSTR_AFTER functions are available for SQL engine. You need to change the execution to SQL engine for the calculated columns However, glad that you could achieve it through another way. 🙂

Below is where you can select the suggested functions.

Best Regards,
Abhi