Q

Joining tables based on partial matches

We have table 1 (t1) and table 2 (t2) full of text/num strings. I need to write a query that will return all the

records in t2 that have an entry in t1 leading the string. For example,

t1 has [ab01, ab02] and
t2 has [ab01234, ab02345, ab01, ab02, ac01]

I would like the query to see if there is anything in t2 that starts with anything in t1.

result [ab01234, ab02345, ab01, ab02]


It looks like your t1 table has 4-character fields. If this is the case, then try the following join --

select t2.* 
  from t1 inner join t2
    on t1.entry = substring(t2.entry,1,4)

This query performs an ordinary inner join.

If the entries in t1 are not all 4 characters in length, then you would need to use TRIM() to grab however many leftmost non-blank characters there are in the t1 field, and use LENGTH() to determine how many characters to match on the t2 field --

select t2.* 
  from t1 inner join t2
    on trim(t1.entry) 
     = substring(t2.entry,1,length(trim(t1.entry)))

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close