How to use subfield function for different separators

[Anonymous]

Hi All,

How to use subfield function for different separators ?

Raw data:

A

Apple : Banana

Orange : Banana

Apple - Banana -Milk

Apple - Banana -Milk - Coffee

Orange : Banana - Tea

Output:

A	B	C
Apple : Banana	Apple	Banana
Orange : Banana	Orange	Banana
Apple - Banana -Milk	Apple	Banana -Milk
Apple - Banana -Milk - Coffee	Apple	Banana -Milk -Coffee
Orange : Banana - Tea	Orange	Banana - Tea

Thanks,

Krishna.

[swuehl]

You can first REPLACE() all different separators to a common one, then use SUBFIELD().

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD A,

SubField(A, ' ', 1) as B,

Mid(A, Len(SubField(A, ' ', 1))+4) as C;

LOAD * INLINE [

    A

    Apple : Banana

    Orange : Banana

    Apple - Banana -Milk

    Apple - Banana -Milk - Coffee

    Orange : Banana - Tea

];

[Anonymous]

Thanks sunny