Any ideas on a Smart Splitter for Text fields

[ryan_smith44]

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.

[former_member187605 · Accepted solution]

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, ' ', '.');