Inside my data flow pipeline I would like to add a derived column and its datatype is array. I would like to split the existing column with 1000 characters without breaking words. I think we can use regexSplit,
regexSplit(<string to split> : string, <regex expression> : string) => array
But I do not know which regular expression I can use for split the existing column without breaking words. Please help me to figure it out.
I created a workaround for this and it works fine for me.
filter(split(regexReplace(regexReplace(text, `[\t\n\r]`, ``), `(.{1,1000})(?:\s|$)`, `$1~~`), '~~'), #item !="")
I think, we have a better solution than this.
I wouldn't use a regex for this, but a truncating function like this one, courtesy of TimS:
public static string TruncateAtWord(this string input, int length)
{
if (input == null || input.Length < length)
return input;
int iNextSpace = input.LastIndexOf(" ", length, StringComparison.Ordinal);
return string.Format("{0}…", input.Substring(0, (iNextSpace > 0) ? iNextSpace : length).Trim());
}
Translated into expression functions it would look something* like this.
substring(Input, 1, iif(locate(Input, ' ', 1000) > 0, locate(Input, ' ', 1000) , length(Input)) )
Since you don't have a lastIndexOf available as an expression function, you would have to default to locate, which means that this expression truncates the string at the first space after the 1000th character.
*I don't have an environment where I can test this.
regexSplit treats matched regex as delimiters that are removed. So I believe any extra letters matched with \S* would be lost.
/.{0,50}\S*(?:\s|$)/m)(?<=.{1000})\s? If Azure applies that iteratively, it would work, but if it applies it all at once on the original string it won't.
