Split string

[#1]

Hi!

I want to split a string on ',' but only if it's not inside paranteses.

Ex. aa bb (c,c) dd,dd ss aa,ee rr ff dd should be three:

aa bb (c,c) dd

dd ss aa

ee rr ff dd

regards

thomas

[#2]

you can try this function

TextBetween( s , beforetext , aftertext [, n ] )

Returns the text between the n:th occurrence of beforetext and the immediately following occurrence of aftertext within the string s.

Examples:

TextBetween('<abc>', '<', '>')	returns 'abc'
TextBetween('<abc><de>', '<', '>',2)

[swuehl]

Thomas,

maybe like this in the script:

TEST:

LOAD subfield(Test,',') as TestSubfield INLINE [

Test

"aa bb (c,c) dd,dd ss aa,ee rr ff dd"

];

tmp:

LOAD *

,if(index(TestSubfield,')') and previous(index(TestSubfield,'(')), peek(TestSubfield)&','&TestSubfield,

if(findoneof(TestSubfield,'()')=0, TestSubfield, NULL())) as Result

resident TEST;

Result:

LOAD Result resident tmp where not IsNull(Result);

drop tables tmp, TEST;

Regards,

Stefan

[#4]

Stefan, this is what I were beginning with. First split and then "repair".Sorry to say that it don't work for my dataset, but I will try to refine it. Not blaming you since you only got a mockup dataset. Attaching an excel file with some data and desired outcome. Ignore Home- and Awayteam

[RSvebeck]

Hi Thomas. I have a solution that might work. See attached. Regards Robert

[#6 · Accepted solution]

This does the trick:

Directory;
TEST:
LOAD
SubField(A,',') as TestSubfield
FROM
stringTest.xlsx
(ooxml, no labels, table is Blad1);
tmp:
LOAD *,
if(previous(index(TestSubfield,'(',2))=0,TestSubfield,
 if(index(TestSubfield,'(',2)>0 and previous(index(TestSubfield,'(',2))>0,TestSubfield,
 if(index(TestSubfield,'(',2)=0 ,peek(TestSubfield)&','&TestSubfield,NULL()))) as Result
resident TEST;
Result:
LOAD Result
 resident tmp 
where not IsNull(Result) AND
SubStringCount(Result,'(')=SubStringCount(Result,')');
DROP tables tmp,TEST;

I'll look at your solution as well Robert since mine probably is "ugly".