Q
Problem solve Get help with specific problems with your technologies, process and projects.

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!


This was last published in March 2005

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close