Spliting string onto new line after every 132 characters in Data Services

[former_member498903]

Hi Guys,

I want to split string onto new line after every 132 characters.

here, for the product Pencil, the description length is more than 132 characters, hence after any complete word character, nearby to 132 character it shall split the string onto new line.

for more understanding of a problem I am sharing the screenshot below:

Kindly suggest.

Regards,

DS_Beginner

[former_member187605]

One query transform will do. Depending on the maximum lenght of your input field, add a 2nd, 3rd... field in the output. And map:

• output_field_1 = substr(input_field, 1, 132)
• output_field_2 = substr(input_field, 133, 132)
• ...

If you want your output in multiple rows instead of columns, add a Pivot transform. Refer to the Data Services Reference Guide for details.

[former_member498903]

Hi Dirk,

I have tried out your solution and it worked pretty well with my requirement but the only concern was it repeats all other field data as well with the additional row information.

As I suggested in my original post was, the additional string information (or the split string) only displayed in the subsequent line.

please check my original post for more clarity and kindly suggest.

Regards,

DS_Beginner

[former_member187605]

The pivot transform generates a sequence column. Use it in an extra query transform for mapping the other fields:

     decode(sequence = 1, <field name>, null)

[Former Member]

First add a column and calculate the maximum length of the text (say 150) . In the next query transform join the incoming source records with a row generation transform having max value as the max length. So you will get as many records rows for each record. In the third query transform where clause write this (ROWID - 1)* varcharlength < length (field)

In the final query transform use a substring -

substr(fieldname, 1+(ROWID-1)*varcharlength,varcharlength)

[former_member498903]

Hi Arun,

As the steps suggested by you above, I am attaching the screenshot for your reference, this is because the output is coming as something different.

1. Row Genera

2. Query 2

3. Query 3

output:: Please check the source and target record.

1. In target the 20th length field is calculated from right

2. The right length characters coming in the first string and the remaining in the 2nd string.

kindly check whether do I need to do any modifications to the above code.

[Former Member]

Hi,

The ROW_ID generation should start from 1 not from 0.

Arun