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 an IT Professional (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)