So from above data how system should know what value is what. For example you said from above data(1st Case) region is missing, how system should know, because system will consider IND as region because its second value.
Hope u understood what I want to say.
you can try this (Not given exact solution).
Please go around with FindOneOf( and SubStringCount( like that ..
What i found is , if you have count of character '/" greater than 5 times only we can able to split...
(html, codepage is 1252, embedded labels, table is @1);
SubStringCount(FullPath,'/') as char_count,
FindOneOf(FullPath,'/') as exactplace,
FindOneOf(FullPath,'/',2) as exactplace2,
Len(FullPath) as fullpathlen
DROP Table SOURCE;
if( char_count>=5, LEFT(FullPath,exactplace-1)) as BusinessFuntion,
if( char_count>=5, MID(FullPath,exactplace,(exactplace2- exactplace))) as Region
DROP TABLE TEMP1;