How to Split Field

[debabrata_sahoo]

Hi Everyone,

Please help me on below example in script level.

Source Table - 
ID      HandlingCode
1        FRO,PER,PHC
2        PES,PHC
3        ICE,PER
4        PER
5        EAP,PER

OutPut Table
ID       HandlingCode1        HandlingCode2        HandlingCode3
1                    FRO                               PER                                   PHC
2                    PES                                PHC
3                    ICE                                 PER
4                    PER
5                    EAP                                PER                                    FRO

[raman_rastogi · Accepted solution]

Use Subfield() function
ex- Load
SubField(HandlingCode,',',1) as HandlingCode1,
SubField(HandlingCode,',',2) as HandlingCode2,
SubField(HandlingCode,',',3) as HandlingCode3
from ..;

[Shubham_Deshmukh · Accepted solution]

Use resident table and create fields like this,

Load
subfield(HandlingCode, ',', 1) as HandlingCode1,
subfield(HandlingCode, ',', 2) as HandlingCode2,
subfield(HandlingCode, ',', 3) as HandlingCode3
.
.

[debabrata_sahoo]

Thank You @raman_rastogi 

Can we have any other way.  ?

[Shubham_Deshmukh]

Hi,
May I know why you want other way? Means any specific requirement?

[debabrata_sahoo]

Due to Low  performance . I already implemented on my Script but its take bit more time to execute.

Here in my script I used to do for 9 handling code Fields.

[Shubham_Deshmukh]

which database you are using? I think, we can also do this in DB script itself.

[debabrata_sahoo]

@Shubham_Deshmukh 

Sql Server. But here I need to do it in script level. Now I am using these script only, which you provided earlier.

[Shubham_Deshmukh]

That's what I am saying, DB has some inbuilt function to do this, now I also don't have exact idea about it but DB has some function like MySQL has Substring_inde().