splitting a field into seperate fields between 2 different delimiters

[hopkinsc]

Hi All,

I have a field (GCELL) that has values like...

LABEL=TEST-1, CellIndex=0, CGI=619054445D955

I want to split these into their own fields as follows..

NewField1

TEST-1

NewField2

0

NewField3

619054445D955

can anyone help please? I have tried the subfield function which works for NewField3 but i can't get the other 2 without duplicating rows of data.

See attached file..

[swuehl · Accepted solution]

Maybe like

Data:

Load *,

  subfield(Subfield(GCELL,',',1),'=',2) as NewField1,

  subfield(Subfield(GCELL,',',2),'=',2) as NewField2,

  subfield(Subfield(GCELL,',',3),'=',2) as NewField3

Resident TestData;

[sunny_talwar]

May be this:

TestData:

Load * inline [

GCELL

'LABEL=TEST-1, CellIndex=0, CGI=619054445D955'

];

Data:

Load *,

  Subfield(Subfield(GCELL,'=',2),', ', 1) as NewField1,

  SubField(SubField(GCELL, '=', 3),', ', 1) as NewField2,

  Subfield(GCELL,'=',4) as NewField3

Resident TestData;

DROP table TestData;

Output:

[anbu1984]

You can get result using TextBetween() just once like below

Load *,TextBetween(GCELL,'=',',',1) as NewField1,
TextBetween(GCELL,'=',',',2) as NewField2,
TextBetween(GCELL &',','=',',',3) as NewField3 inline [
GCELL
'LABEL=TEST-1, CellIndex=0, CGI=619054445D955'
];