0

I have a column named id in two different tables, table1 and table2 which always contain 30 characters. I want to select from table1 and table2 where the 6th to 30th character of the id column in both tables are the same. The id column is unique in both tables.

Uchenna Nwanyanwu
  • 3,174
  • 3
  • 35
  • 59

3 Answers3

2
select * 
from table1
join table2 on substring(table1.id, 6) = substring(table2.id, 6)
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • @UchennaNwanyanwu: Please don't change the code of an accepted answer if you are not really absolutely sure what you are doing. I rolled your change back. – juergen d Jul 22 '14 at 21:54
  • 1
    Additional explanation: The question said "where the 6th to 30th character (of 30 total) ... are the same". That means only the first five characters may differ. Because of that the substring has got to use all characters beginning with the 6th until the end => `substring(tableN.id, 6)` (N = 1 or 2). – VMai Jul 22 '14 at 22:07
  • @juergend I made the modification before accepting the answer. It is also a good practice on stackoverflow to modify answers whether accepted or not in order to provide clarity to future users. In this case, I posted the question and applied the answer. Invariably, I understand what I want and that is the reason for the changes that I made. Just because you answered a question does not mean that the poster is not really sure what he is doing. – Uchenna Nwanyanwu Aug 14 '14 at 09:28
2

You can do this without regex, and I like the right function for this (returns n rightmost characters):

SELECT *
  FROM table1 t1
  JOIN table2 t2
    ON RIGHT(t1.id,25) = RIGHT(t2.id,25)

Actually on second thought the SUBSTRING way is probably better in this instance, just in case a rows end up with shorter ids.

Arth
  • 12,789
  • 5
  • 37
  • 69
0

Try this

SELECT *
FROM table1
JOIN table2 ON SUBSTRING(table1.id,7) = SUBSTRING(table2.id,7);
Uchenna Nwanyanwu
  • 3,174
  • 3
  • 35
  • 59
Sadikhasan
  • 18,365
  • 21
  • 80
  • 122
  • 1
    Please don't use the legacy implicit join sytax. Using explicit joins you can differ between the join conditions and the filter clauses in the `where` condition. – juergen d Jul 22 '14 at 10:22
  • Thanks for suggession. Can you tell me what is different implicit and explicit join instead of readibility? – Sadikhasan Jul 22 '14 at 10:23