How to split data into multiple columns based on condition?

[Anonymous]

Hi All,

How to split data into multiple columns based on condition?

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.

[shraddha_g]

Try

subfield(A,':',1) as B

Subfield(A,':',2) as C

[Anonymous]

Hi Shraddha,

Thanks for u r replay,but the out put is not  showing some records like with '-'

.   

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

Thanks,

Krishna

[shraddha_g]

Try

if(index(A,':')>0,subfield(A,':',1) ,subfield(A,'-',1) ) as B,

if(index(A,':')>0,subfield(A,':',2) ,subfield(A,'-',2) ) as C,

[Anonymous]

In your Example you're using different separators, is that so?