Remove repeated characters and extra names

[bruce_sorge]

Hello all,

I have a field in my DB that looks like this:

Field name is PRI_PI:

Elimelech, Menachem~0,2002,2003,F|Harvey, Ronald W.~0,2002,2003,F

In my load script, I can remove the tilde and numbers like this:

PurgeChar(PRI_PI,'1234567890~-') as PrimaryPI,

I then have this:

Elimelech, Menachem,,,F|Harvey, Ronald W.,,F

What I would like to to is to transform this to:

Elimelech, Menachem | Harvey, Ronald W

Then, when I display the list, each researcher is on their own row:

Elimelech, Menachem

Harvey, Ronald W

How could I accomplish this? 

Thanks

[HirisH_V7 · Accepted solution]

Hi you can use this below,

Purgechar(Subfield(SubField(Field,'|'),'~',1),'.') as Field_Formed.

[Vegar]

Try this is expression:

LOAD 

Subfield( Subfield (PRI_PI,'|'), '~', 1) as Researcher

From data;

[bruce_sorge]

That worked, thanks