Sql: Join Tables on Substrings

SQL: Join tables on substrings

You can use like

select *
from A
  inner join B 
    on B.b like '%'+A.a+'%'

How to join two tables based on substring values of fields?

So many ways to do this. It would be a good idea to look at the explain plan for various ways before committing to a particular method. For example, if there is a function-based index on EMPLOYEE such as SUBSTR(id, 2, LENGTH(id) - 1) then you'll want to use that in your query:

SELECT e.name, i.name
  FROM employee e INNER JOIN instructor i
    ON SUBSTR(e.id, 2, LENGTH(e.id) - 1) = SUBSTR(i.id, 2, LENGTH(i.id) - 1);

Another question is if the values in the id column are always the same length in EMPLOYEE and INSTRUCTOR. What if they are of differing lengths? Maybe one has more padding than another. Also, will they always be digits apart from a leading u? If so, then it might be worthwhile to try a safe TO_NUMBER() conversion:

SELECT e.name, i.name
  FROM employee e INNER JOIN instructor i
    ON TO_NUMBER(REGEXP_SUBSTR(e.id, '\d+$')) = TO_NUMBER(REGEXP_SUBSTR(i.id, '\d+$'));

One other thing you may want to consider, however -- is there a reason for the leading u in the EMPLOYEE id column? Can there be other leading characters? Does the leading u stand for something (violating first normal form, but that happens)?

Need to join 2 tables using substring of different lengths on one column with length stated in second table

I don't know if you're wanting this exactly, but i took correct output for the example table with this.

SELECT RefNr
      ,tb2."Substring Table1 based on length in Table2"
      ,tb2.Name
FROM Table1
INNER JOIN
(SELECT SUBSTRING(ShortRef, 0, Length+1) as "Substring Table1 based on length in Table2",
        Name,
        Length
 FROM Table2) as tb2
ON SUBSTRING(RefNr, 0, tb2.Length + 1) = tb2."Substring Table1 based on length in Table2"

How to join a substring of a column (in one table) with the full value of another column (in another table)

You might join with the condition :

substr(t2.CODE_METADATA,1,instr(t2.CODE_METADATA,'|')-1) = t1.CODE

How can I join tables on a substring in a record?

This would work if you aren't able to put the account_id in the Info table:

SELECT A.Name,
  I.Information
FROM Information I
JOIN Account A
  ON A.account_id = REVERSE(SUBSTRING(REVERSE([account_uri]),0,CHARINDEX('/',REVERSE(account_uri))))

The REVERSE(...) is being used to create a LastIndexOf function.

Might want to just do:

SELECT REVERSE(SUBSTRING(REVERSE([account_uri]),0,CHARINDEX('/',REVERSE(account_uri))))
FROM Information

To see if the SUBSTRING/REVERSE are getting the entire account id

This should find the second string between '/'

SUBSTRING(REVERSE(SUBSTRING(REVERSE([account_uri]),CHARINDEX('/',REVERSE(account_uri)))),0,CHARINDEX('/',REVERSE(SUBSTRING(REVERSE([account_uri]),CHARINDEX('/',REVERSE(account_uri)))))

Joining two tables where the join condition requires a substring

I was able to solve this in the end by padding out SUBSTR(A.ref, 2,10) to 12 characters.

---

Related Topics