I have a string field (Resident Name) in my database that I need to parse to be First Name and Last Name.
Seems easy - I have it working most of the time.
Sometimes my string field has a & character to indicate a married couple.
I need to modify my script to work when the resident name is formatted with a &.
Here's a picture of the dataset:
Here's my script for first name and last name being 'parsed' out of the resident name
LEFT([ResidentName], charindex(' ', [ResidentName]) - 1) as [First Name],
SUBSTRING([ResidentName], CHARINDEX(' ', [ResidentName]) + 1, LEN([ResidentName]) - CHARINDEX(' ', [ResidentName])) as [Last Name],
How can I modify my LEFT syntax to be something like left of & - rather than simplistic left of the first space if there is a & or simply left of first space if we don't have a & in the field?
Thanks in advance,
Bron