Subfield with leading and/or trailing separator creates difficult to remove character

[Tom_Mitchell]

Has anyone come across the SubField() function creating a difficult to remove character when the string you give it contains leading and/or trailing separator characters?

The below example produces what appears to be an empty row, but copying the contents of the cell into notepad++ suggests its a NBSP, and using ORD() suggests its a Chr(0). 

I have tried PURGECHR(), KEEPCHR(), and TRIM() but nothing seems to remove it. The only success I have had is making an if statement around [String] to remove the leading and trailing ';' values - which works, but isn't ideal because the actual data can have the ';' value appearing in 4 different patterns.

Script:

LOAD 

 SubField(String, ';') AS [Strings] 

INLINE [

String

;abc;def;ghi;

];

Result:

[Or · Accepted solution]

Perhaps just use a preceding load to check if the length of the outcome string is greater than zero?

Load Strings
Where Len(Strings)>0;
LOAD

SubField(String, ';') AS [Strings]

INLINE [

String

;abc;def;ghi;

];

Should also be fairly easy to check if the leftmost string is the separator and if so, use Mid(String,2,9999) instead of the full string.

[Tom_Mitchell]

@Or  you are right, the preceding load with where clause does remove it. I did try, and obviously failed somehow. 

Then PurgeChar() and KeepChar() don't remove the problem, because the blank value doesn't exist until the SubField() has done its job. 

LEN() appears to be the only way to remove unwanted value. Wonder if the SubField function could be improved to not generate an empty value, or at least mention it in the Help page for SubField.

Thanks @Or  for the answer 🙂