Joining on a substring of a column

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".


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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!


This was first published in March 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top