Split comma separated column data into multiple columns

General
,
1

Hello Guys,

I need a help with source code for my function alias, the requirement is as below.

I have a property which will have a value like Test1|Test2|Test3|Test4, I have to apply function for report column to show Test2 in one column and same function for another column to show Test3. Ideally, I am thinking to create a function alias that should trim accordingly.

Thanks in advance :slight_smile:

2

@BATTINIRAMESH This is what I found online:

To achieve this, you can create a function alias that takes two input parameters: the property containing the values (e.g., Test1|Test2|Test3|Test4) and the index of the desired value (e.g., 2 for Test2 or 3 for Test3). In the function alias implementation, you can split the input property value using the ‘|’ delimiter and then return the value at the specified index.

For example, in a Java function alias, you can use the following code snippet:

String values = propertyValue.split(“\|”);

if (index >= 0 && index < values.length) {

return values[index];

} else {

return “”;

}

After creating the function alias, you can use it in your report definition columns by specifying the property and the desired index as input parameters.

alternatively:

To split values in a property into separate columns and trim them, you can use the following steps:

Create a new report definition or update an existing one.

In the report definition, add a new column for each value you want to split.

For each new column, use a custom SQL function to extract the desired value from the original property and trim it.

For example, if you have a property with comma-separated values, you can use the SQL function SUBSTRING_INDEX to extract the values and TRIM to remove any extra spaces:

For the first column: TRIM(SUBSTRING_INDEX(YourProperty, ‘,’, 1))

For the second column: TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(YourProperty, ‘,’, 2), ‘,’, -1))

For the third column: TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(YourProperty, ‘,’, 3), ‘,’, -1))

Replace ‘YourProperty’ with the name of the property you want to split, and adjust the numbers in the function according to the position of the value you want to extract.

Please note that the exact SQL functions and syntax may vary depending on the database you are using. Consult your database documentation for the appropriate functions and syntax.

Note:

This answer came from a Pega gen-AI assistant using the below references. As a Support Center moderator, I reviewed the answer and references for accuracy:

Function Alias rules — Completing the Create, Save As, or Specialization form

Function Alias rules > SQL expressions { .section}

Adding columns to report

3

Hello @MarijeSchillern ,

Thank you so much for the helpful information. I did tried the above mentioned approach using sql function in function alias rule and it got worked.

But the only change I did is, I used split_part function instead of SUBSTRING_INDEX. As SUBSTRING_INDEX function is not supported by the PostgreSQL.