Split one field into two columns with unorganized strings and conditional character

[leey8211]

Hello everyone, I have a column with unorganized strings like this, for example: 

      apple; NCST-00345; QTT; 83457; w456-IOKY-34569865

      banana; NEL-00985; QWZ; 345876; w-9845-mbjkh-984039842

      avocado; 345; SGY-983457; 2344; wPOierty

      ........ 

The goal is to split this column into two columns. One column contains all the strings before "w", the other column would be include everything with "w" and after "w", like this: 

original	Column one	column two
apple; NCST-00345; QTT; 83457; w456-IOKY-34569865	apple; NCST-00345; QTT; 83457;	w456-IOKY-34569865
banana; NEL-00985; QWZ; 345876; w-9845-mbjkh-984039842	banana; NEL-00985; QWZ; 345876;	w-9845-mbjkh-984039842
avocado; 345; SGY-983457; 2344; wPOierty	avocado; 345; SGY-983457; 2344;	wPOierty
................	............	.........

So, how can I write set expressions or script code to achieve this goal? Or, what functions should I use? I tried SubField function but it's not working well. Any advice or hint would be greatly appreciated, many thanks! 

[PrashantSangle · Accepted solution]

Hi,

use mid() & index() to achieve the requirement

try below

Load Test, Mid(Test,1,Index(Test,'w')-1) as column1, Mid(Test,Index(Test,'w')) as column2 Inline [
Test
"apple; NCST-00345; QTT; 83457; w456-IOKY-34569865"
];

Regards,

Prashant Sangle

[vinieme12]

if its always the last ';' delimited string you can also use subfield

load original
,subfield(original,subfield(original,';',-1),1) as firstCol
,subfield(original,';',-1) as secondCol
inline
[
original
apple; NCST-00345; QTT; 83457; w456-IOKY-34569865
banana; NEL-00985; QWZ; 345876; w-9845-mbjkh-984039842
avocado; 345; SGY-983457; 2344; wPOierty
];

[leey8211]

Thank you Prashant, your solution works!

[leey8211]

Thank you Vineeth, it helps!