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 [email protected] 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.