Skip to Content

BODS : How to split Long string in a column to multiple segments in new columns

Hi All,

There's a issue and I need help in fixing that.

Scenario :

There's a source column of varchar(8000) length. Now target accepts maximum 132 char in one column. So, if there's a string coming >132 characters in input column, the String Must be broken down in SEQUENCE of 132 character segments and loaded in output columns(which are of varchar(132) size).

For example :

If input came 600 characters long in source Column. Then this input must be sequentially splitted into 5 output columns (132+132+132+132+72). First four columns should contain 132 characters each and Fifth column should contain leftover 72 characters.

Request you all to help me asap. Please help me in building Custom function for this.

Thank you in advance.

Samarth

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jul 05, 2017 at 09:16 AM

    You cannot solve this in a custom function, because a DS function returns a single value only.

    You can solve that by writing Python code in a User_Defined transform.

    Or map all output columns individually in a Query transform:

    • Col1: substr(source_column, 1, 132)
    • Col1: substr(source_column, 133, 132)
    • Col1: substr(source_column, 265, 132)
    • ...

    No need for any ifthenelses. If the starting position is higher than the source_column length, the result will be null. If the length of the segment is less than 132, only the existing content is copied.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 05, 2017 at 08:29 AM

    8000 char is a too long string and if you try to break down to 132 chars each there will be around 60 columns for you to store data in.

    If you are OK with having 60 columns then you can have a check in each column on length and use substring function to populate that.

    Column 1 - ifthenelse(length(source_column) > 132, SUBSTR(source_column,1,132), SUBSTR(source_column, 1, length(source_column)) )

    Column 2 - ifthenelse(length(source_column) > 132*2, SUBSTR(source_column,132+1,132*2), SUBSTR(source_column, 132+1, length(source_column)) )

    Column 3 - ifthenelse(length(source_column) > 132*3, SUBSTR(source_column,132*2 + 1,132*3), SUBSTR(source_column, (132*2)+1, length(source_column)) )

    ....and so on.

    Alternatively you might want to have a look at Pivot Transform for your task.

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 31 at 05:54 AM

    Hi Saxena,

    I am also having the same requirement. Could you please tell me how did you implement this in BODS?

    Regards,

    KT

    Add comment
    10|10000 characters needed characters exceeded