Joining on a substring of a column
Using MS Access:
SELECT * FROM EEqry E, LoanOfficers L WHERE E.FirstName = L.FirstName
How can I change this SQL statement so that both FirstName fields only compare the value up to the first space? What I'm try to accomplish is if E.FirstName = "John T." and L.FirstName = "John" or E.FirstName = "John T." and L.FirstName = "John C." they both would be equal because they would only be comparing the string, "John".
In standard SQL, this is accomplished using string functions:
select * from EEqry E inner join LoanOfficers L on case when position(' ' in E.Firstname) = 0 then E.FirstName else substring(E.FirstName from 1 for position(' ' in E.Firstname)-1) end = case when position(' ' in L.Firstname) = 0 then L.FirstName else substring(L.FirstName from 1 for position(' ' in L.Firstname)-1) end
Notice how a CASE expression is used to ensure that if the name does not actually contain a space, then the comparison will use the whole name.
In Access syntax, this becomes:
select * from EEqry E inner join LoanOfficers L on iif(instr(E.Firstname,' ') = 0 , E.FirstName , left(E.FirstName , instr(E.Firstname,' ')-1) ) = iif(instr(L.Firstname,' ') = 0 , L.FirstName , left(L.FirstName , instr(L.Firstname,' ')-1) )
Some people dislike Microsoft Access because its version of SQL is [quote]so[unquote] non-standard, but honestly, it's not that bad once you get to know it!