Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a field containing data like
AL , R , 267593 , |
I can use the SUBFIELD Function to seperate the data with a result of
AL
R
267593
But I also get a blank field because of the last comma, how can I remove these blank fields?
All the data in the field ends with a , (comma)
In order to do it, you can use function Left and take all string except the last one char
i.e.
subfield(left('ColName',len('ColName')-1), ',')
Maybe like
LOAD FIELD WHERE Len(Trim(SUBFIELD));
LOAD Subfield(FIELD, ',') as FIELD FROM ....;
In order to do it, you can use function Left and take all string except the last one char
i.e.
subfield(left('ColName',len('ColName')-1), ',')
Table:
LOAD trim(F1) as FieldList
INLINE [
F1
"AL , R , 267593 ,"
"AL , R , 2rqwrwe ,"
];
LOAD
SubField(FieldList,',') as Field;
LOAD
if(
right(FieldList,1) = ',',
mid(FieldList,1,len(FieldList)-1),
FieldList
) as FieldList
resident Table;
Hi,
Left() and len()-1
I do not see (now) what to use instead ...
Fabrice
Hi Swuehl,
I tried using
but I get an error saying SUBFIELD requires 2-3 parameters
Thanks Jacob
this seems to work thanks
Alan
Sorry, I edited my post and forgot the WHERE clause (it should be FIELD instead of SUBFIELD)
Maybe like
LOAD FIELD WHERE Len(Trim(FIELD));
LOAD Subfield(FIELD, ',') as FIELD FROM ....;