I had a request to produce a SQl view for a client recently which extracted the first email address from the EmailToAddress field in the Address Electronic Funds Transfer Master (SY06000) table linked to a creditor record in Microsoft Dynamics GP. This field generally stores a single email, but sometimes stores multiple email addresses separated with a semi-colon.
The below script will extract the first email address from the field if it is delimited with a semi-colon or the entire content of the field if there is no semi-colon.
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ SELECT CASE WHEN SUBSTRING(EmailToAddress, 0, CHARINDEX(';', EmailToAddress)) = '' THEN EmailToAddress ELSE SUBSTRING(EmailToAddress, 0, CHARINDEX(';', EmailToAddress)) END AS Email FROM SY01200 -- Address Electronic Funds Transfer Master (SY06000)