how to split the data into new record

[muniyandi]

Hi Team,

i have source table record like this

Source Records
"AA"-"AB"-"CC"-"DD"
"AM"-"AD"
"AH"-"AN"-"BL"

I need to load the data below mentioned format.

Expected Result
AA
AB
CC
DD
AM
AD
AH
AN
BL

[sunny_talwar]

May be try this:

PurgeChar(SubField(Source, '-'), Chr(34)) as Expected

[sunny_talwar]

For your sample:

Table:

LOAD *,

  PurgeChar(SubField([Source Records], '-'), Chr(34)) as Expected;

LOAD * INLINE [

    Source Records

    '"AA"-"AB"-"CC"-"DD"'

    '"AM"-"AD"'

    '"AH"-"AN"-"BL"'

];

[muniyandi]

Thanks Sunny T

Some data have number also

"AH#2"-"AN#6"-"BL#8"

[sunny_talwar]

Would this be the output you would want to see?

Still using the same script:

Table:

LOAD *,

PurgeChar(SubField([Source Records], '-'), Chr(34)) as Expected;

LOAD * INLINE [

    Source Records

    '"AA"-"AB"-"CC"-"DD"'

    '"AM"-"AD"'

    '"AH"-"AN"-"BL"'

    '"AH#2"-"AN#6"-"BL#8"'

];

[Kushal_Chawda · Accepted solution]

try this

Subfield(keepchar(Field,'ABCDEFGHIJKLMNOPQRSTUVWXYZ-'),'-') as Result