It took me a while to troubleshoot this, but I kept on having a mapping return an empty string for some rows when I wasn't expecting it.
A common pattern in my data migration is that I need to map a character column from a source system to target, but the target column is smaller than the source (e.g. going from a 255 char column to 40 char). For this particular migration, only the first 40 characters are relevant, so I thought I could do:
$0.TargetColumn = left($0.SourceColumn, 40);
However, I found that if a particular row has LESS than 40 characters (but is not blank), TargetColumn will be set to empty/null for that row.
Shouldn't the desired/expected behavior of left(string, count) be that if the string has less than the <count> specified, return the entire string? This is how most (if not all) Java, VB, etc. functions work.
A common pattern in my data migration is that I need to map a character column from a source system to target, but the target column is smaller than the source (e.g. going from a 255 char column to 40 char). For this particular migration, only the first 40 characters are relevant, so I thought I could do:
$0.TargetColumn = left($0.SourceColumn, 40);
However, I found that if a particular row has LESS than 40 characters (but is not blank), TargetColumn will be set to empty/null for that row.
Shouldn't the desired/expected behavior of left(string, count) be that if the string has less than the <count> specified, return the entire string? This is how most (if not all) Java, VB, etc. functions work.
-
Hello,
I've created an issue for the problem (http://bug.cloveretl.org/view.php?id=5861) in our bug tracking system. As a workaround you can use function left(string input, integer length, boolean spacePad), that returns prefix of the specified length, but if input string is shorter than specified length and 3th argument is true, right side of result is padded with blank spaces so that the result has specified length. With the false value of 3rd argument, the function works as function left(string input, integer length) should work.
Please sign in to leave a comment.
Comments 1