Jump to content
TNG Community

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

Recommended Posts


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.





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