Skip to Content

Any ideas on a Smart Splitter for Text fields

I have an unusual issue that I am hoping someone may have ideas for, I have five legacy fields of 60 Character text each, that I need to migrate to eleven fields of 30 Characters each. The issue we are running into is that we have several instances where the 30th character is in the middle of a word so that the output in SAP looks like....

"The Quick brown fox jumped over the la

zy dog, and the dish ran away with the s

poon"

Does anyone have an idea for a "Smart Splitter" that will split these columns on the last space in the text field before the 30th character, but also adjust the additional lines of text after? Meaning, if the code splits on the 26th character in text field 1, then text field 2 would need to pick up at the 27th character, then look for a space before it reaches it max of 30 characters. This is for 20K records and I'm stuck.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Sep 11, 2019 at 02:52 PM

    I have a function that inserts blanks between words in a sentence so that you can split it in even chunks without having to break a word in two.

    Call it with 3 parameters: <your concatenated input>, 30, 330

    Make sure to insert a blank in between each column when you concatenate the input. Then split the result into 11 30-char fields.

    # inserts extra spaces between words in a sentence 
    # so that the input can be split in fields of the same lenght 
    # without having to break a word
    # 
    # returns null if a word is longer than the field length
    #
    # parameters
    #   $Input varchar(1024)
    #   $FieldLength int
    #   $TotalLength int
    # local variables
    #   $Blanks varchar(1024) - string of blanks
    #   $First varchar(1)
    #   $Last varchar(1)
    #   $FirstPos int
    #   $LastPos int
    #   $Output int
    
    
    $Blanks = ' ';
    $FirstPos = 1;
    while($FirstPos < $Fieldlength )
        begin
        $Blanks = $Blanks || ' ';
    	$FirstPos = $FirstPos + 1;
        end
    $LastPos = $FieldLength;
    $FirstPos = $FieldLength + 1;
    $Output = $Input;
    
    
    while (substr($Output, $LastPos, $TotalLength) <> '')
        begin
        $Last = substr($Output, $LastPos, 1);
        $First = substr($Output, $FirstPos, 1);
        while ( $Last <> '' and  $First <> '')
    	    begin
    	    $LastPos = $LastPos - 1;
    	    if ($LastPos < $Firstpos - $FieldLength) return null;
    	    $Last = substr($Output, $LastPos, 1);
    	    end
        if ($First = '')
    	    $Output = substr($Output, 1, $LastPos) || ( substr($Output, $FirstPos + 1, 1024));
    	else
    	    $Output = substr($Output, 1, $LastPos-1) || substr($Blanks, 1, $FirstPos - $LastPos) || ltrim_blanks( substr($Output, $LastPos + 1, 1024));
        $FirstPos = $FirstPos + $FieldLength; 
    	$LastPos = $FirstPos - 1;
    	end
    
    
    return $Output;
    
    
    #return replace_substr( $Output, ' ', '.');
    
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.