Jump to content
TNG Community
Horsman_ONS

Spliting the 'Also Known as' field into forenames and surname

Recommended Posts

Horsman_ONS

I am trying to do a report of ladies who have alternative names.  Can someone advise me how to split the 'Also Known as' field  [e46.info] into forename(s) and surname, so I can compare the surname with a person's 'lastname'.   Example I am wanting to find the lastname of Rosa Maria Barchard,  so need to work from right to left.  Someone has suggested SUBSTRING_Index, but I can't get it to work.

This is the query so far:

SQL: SELECT p.personID, CONCAT(p.firstname,' ',p.lastname) AS Name, e46.info AS Also_Known_As, p.birthdate, p.deathdate, b.description AS Branch, p.gedcom FROM 0156ab_people AS p LEFT JOIN 0156ab_events e46 ON (p.personID = e46.persfamID AND p.gedcom = e46.gedcom AND e46.eventtypeID = "46" ) LEFT JOIN 0156ab_branches as b ON (p.branch=b.branch) WHERE (e46.eventtypeID = 46) AND ((e46.info) NOT LIKE (p.lastname) AND (e46.info) NOT LIKE (p.firstname) AND (p.sex ="F") AND (p.living = '0')) ORDER BY p.lastname

This report picks up ladies where the first names may differ, and what I really want is those where the last names are different; hence the desire to split the field.

Can any one help.

 

Sue

horsman.one-name.net

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×