Skip to Content
Technical Articles
Author's profile photo Andrey Vaniaev

SAP Data Services – Easy way to split long string into equal parts

Sometimes you need to split long (or very long) string into equal parts (e. g. to load it to STXL, STXH SAP Tables). There is pretty easy way to do it, using SAP Data Services standard tools and transforms:

Source data

As a source, we have string with length 617 symbols, and we want to split it into equal pieces of 132 symbols each.

Data Flow

To build this Data Flow we need only 1 Row Generation transform, source (e.g. Excel workbook format), 2 Query transforms and target (e. g. template table)

  1. Row Generation transform: Row number starts at 0, Row count equals Length of the longest string divided by Chunk size (Round up the result), Join rank is 0.
  2. Query transform “Split”: Map DI_ROW_ID from Row_Generation and create new Substring column of desirable size. Change Substring mapping as it shown below:
    substr(<source_string>, (Row_Generation.DI_ROW_ID*<chunk_size>)+1, <chunk_size>)​

  3. Query transform “Get_Not_Null”:  Row Count (see paragraph 1)  corresponds to amount of substrings, that will be generated for each long string. If you have multiple strings in your source dataset, there will be NULL values generated for shorter ones. Exclude such records on WHERE tab.

Result

After executing the job, you will get splitted string(s), as shown below:

 

Main disadvantage of given approach is Row Count, that you have to calculate and set in Row_Generation transform. But anyway, it is much faster than create bunch of columns with substr() functions + Pivot transform or than create custom functions/user defined transforms.

 

 

 

 

 

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mukesh Kumar
      Mukesh Kumar

      Excellent Andrey Vaniaev … Another nice and handy trick from you! I have a question: how to handle it If the input file has multiple long strings instead of one in this example?

      Author's profile photo Andrey Vaniaev
      Andrey Vaniaev
      Blog Post Author

      Hello. Thank you for your reply.

      It works for multiple strings too!

      Author's profile photo Mukesh Kumar
      Mukesh Kumar

      Cool… Thank you for the quick reply Andrey Vaniaev Probably we need to do something then to identify the original long strings in the target table.

      Author's profile photo Andrey Vaniaev
      Andrey Vaniaev
      Blog Post Author

      Just add identifier next to long string, so every substring will have substring ID and long string ID. After that you will be able to put it all together again, if it necessary.

      Author's profile photo Mukesh Kumar
      Mukesh Kumar

      Yeah... Spot on. Thanks!

      Author's profile photo Dirk Venken
      Dirk Venken

      See ​my answer to Any ideas on a Smart Splitter for Text fields for an alternative approach, splitting on word boundaries.