How to extract a delimiter

[rdsuperline]

I want to extract delimiter. I understand that Subfield function can be used if we want to get content before or after delimiter. But in my column named Location values are

Boxborough, MA

Brazil, Sao Paulo

Bridgewater, NJ

California - San Diego

Canada, Markham

Using below code in the script I am able to extract the values in green, but not the value in red

if("NH Country" = 'USA',TRIM(Subfield("LOCATION",',',1)),(if("NH Country" <> 'USA',TRIM(Subfield("LOCATION",',',2))))) AS "PLCity",

Can someone help?

Thanks

[Vegar]

You could try to replace all - with a ,  before performing your trim logic.

[rdsuperline]

Thanks but when there is a - I need to pick city after the delimiter and when there is , I need to pick city before , so the replace logic wont work correctly

[marcus_sommer]

The delimiter in both subfields is the same and therefore the logic couldn't work. Personally I would follow the suggestion from Vegar to unify the different delimiter and then transferring the condition into the third parameter of the subfield, means something like this:

trim(subfield(replace(LOCATION, '-', ','), ',',
   if([NH Country] <> 'USA' or substringcount(LOCATION, '-'), 2, 1)))

Depending on the variety of your delimiter and/or the order in which the values are placed you might need to add further conditions and/or to adjust them here. If the variety of the above mentioned is quite big but there aren't really many values it might be an alternatively to create a (manually) mapping table for it and replace the values with it.

- Marcus

[Brett_Bleess]

I may be way off-base here, but what about two different loads of the data, one where you grab all the records with the ,'s and another where you grab the -'s, that way your subsequent logic can be properly applied to each and you can then join things properly from those two Resident tables you end up having?  Apologies again if I am way off here, but from what I could gather, this seemed like a potential way to go with things given the use case.

Regards,
Brett