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.