Replace with String Contains

[settu_periasamy]

Hi,

I need to replace the string based on between the values(like textbetween). see the example below.

Need to replace the String ': * |' as blank. (note :  '*' contains any length)

FieldOne: raja | FieldTwo: India | FieldThree: Calculated |

result should be :

FieldOne FieldTwo FieldThree

i tried the below expression

'=replace('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |',':'&chr(42)&'|',' ')

Thank You.

[ToniKautto]

That's gonna be hard in a sheet object. I would suggest doing it in the script, so your data is pre-formatted for presentation.

LOAD
  SubField(F1, ':', 1) AS F1
;
LOAD 
  SubField(F1, ' | ')  AS F1
Inline [
F1
FieldOne: raja | FieldTwo: India | FieldThree: Calculated |
];

[senpradip007]

Try with

=PurgeChar('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |', ':|')

[danieloberbilli]

LOAD *,

left(F1, Index(F1,':',1)-1) &' '&

    mid(F1,Index(F1,'|',1)+1,Index(F1,':',2)-Index(F1,'|',1)-1 ) &' '&

    mid(F1,Index(F1,'|',2)+1,Index(F1,':',3)-Index(F1,'|',2)-1 ) as Result

;

LOAD * INLINE [

    F1

    FieldOne: raja | FieldTwo: India | FieldThree: Calculated |

];

[jagan]

Hi,

Try like this in script

Temp:

LOAD

  RecordNum, 

  RowNo() AS Num,

  SubField(F1, ':', 1) AS F1 

; 

LOAD RecNo() AS RecordNum, 

  SubField(F1, ' | ')  AS F1 

Inline [ 

F1 

FieldOne: raja | FieldTwo: India | FieldThree: Calculated | 

];

Data:

LOAD

RecordNum,

CONCAT(F1, ' ', Num) AS Value

RESIDENT Temp

GROUP BY RecordNum

ORDER BY Num;

DROP TABLE Temp;

OR if you want to do it in front end try like this

=SubField(SubField('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |', ' |', 1), ':', 1) & ' ' & SubField(SubField('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |', ' |', 2), ':', 1) &

SubField(SubField('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |', ' |', 3), ':', 1)

Hope this helps you.

Regards,

Jagan.

[settu_periasamy]

Thanks Jagan.

I want to do this in front end.  The given expression is fine.

But if I have Large String,  is any other function is available?

Regards,

Sethu

[settu_periasamy]

Hi,

I tried this. But not getting the expected result.

[ToniKautto]

I assume the long string comes from the loaded data, so why do you want to process it in run-time instead of pre-processing it during reload?

I realized I forgot to add the concat in my example.

LOAD Concat(F1) AS F1;
LOAD SubField(F1, ':', 1) AS F1;
LOAD SubField(F1, ' | ')  AS F1
Inline [
F1
FieldOne: raja | FieldTwo: India | FieldThree: Calculated |
];

[danieloberbilli]

My example from above also works in the frontend...just type the following expression in a textbox or chart and replace the field name

left(F1, Index(F1,':',1)-1) &' '&

    mid(F1,Index(F1,'|',1)+1,Index(F1,':',2)-Index(F1,'|',1)-1 ) &' '&

    mid(F1,Index(F1,'|',2)+1,Index(F1,':',3)-Index(F1,'|',2)-1 )

[settu_periasamy]

Thanks Toni. I will try to implement this in the script.