Skip to Content
0

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

Jul 03, 2017 at 06:30 AM

496

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Dirk Venken
Jul 05, 2017 at 09:16 AM
1

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.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Firstly, Thanks a lot Dirk for sharing your immense knowledge in solutions.

There's extension to this scenario. Request you to please help.

1. For example input string is of 200 characters. In First segment of 132 characters, if last word is getting cut i.e last word is getting few characters missed, then it must be considered as starting word of next segment. It should not come in first segment then i.e. first segment length will be less than 132 characters in this case. This should be taken care in all output columns.

2. Is there any way in BODS, where we can make this dynamic? I mean no fix output columns. If source string length is 600 characters, then it generates output in 5 columns, if it is 700 characters then 6 columns and so on. I mean otherwise, how to certain the no. of output columns? If I define column mappings for 5 columns today and next time a bigger input comes, then it's a problem. Can we generate the output structure dynamically depending on input?

It's urgent. Your help is deeply appreciable.

0
  1. Can be solved with a custom function. Look for the last space in the first 132-character segment. Replace that space by a character that doesn't exist in your texts,e.g. µ. Take the next 132-character segment, starting from the µ-position + 1, look for the last space and replace... Repeat this process until you reach the end of the string..
    Return the string with spaces replaced by µ's.
    Then use word_ext(source_column, 1, '`'), word_ext(source_column, 2, '`')... to map to the output fields.
  2. This cannot be made dynamic. You know your max string length is 8000, correct? Then go for e.g. 80 columns to be on the safe side.
    If you don't want to store all those nulls, consider pivoting your data and filter out the null rows.
0

Hi Dirk,

Point no.2 is clear. However, Can you please elaborate point no.1? How can we implement it?

I tried, but not getting it.

Thanks

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

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hey Shubham,

Thanks a lot for the solution. It did help me.

Request you to look into extension to this scenario in my reply to Dirk (above).

Please share your inputs whatever you can suggest. It'll be really appreciable.

Thanks

Samarth

0
Former Member

Suggestions given by Dirk above are just perfect.

0