Using SUBFIELD for Comma seperated values

[rustyfishbones]

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)

[swuehl]

Maybe like

LOAD FIELD WHERE Len(Trim(SUBFIELD));

LOAD Subfield(FIELD, ',') as FIELD FROM ....;

[#3 · Accepted solution]

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

[Clever_Anjos]

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;

[#5]

Hi,

Left() and len()-1

I do not see (now) what to use instead ...

Fabrice

[rustyfishbones]

Hi Swuehl,

I tried using

but I get an error saying SUBFIELD requires 2-3 parameters

[rustyfishbones]

Thanks Jacob

this seems to work thanks

Alan

[swuehl]

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 ....;