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.
Add a comment
|
3 Answers
select *
from table1
join table2 on substring(table1.id, 6) = substring(table2.id, 6)
3 Comments
juergen d
@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.
VMai
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).
Uchenna Nwanyanwu
@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.
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.
Comments
Try this
SELECT *
FROM table1
JOIN table2 ON SUBSTRING(table1.id,7) = SUBSTRING(table2.id,7);
2 Comments
juergen d
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.
Sadikhasan
Thanks for suggession. Can you tell me what is different implicit and explicit join instead of readibility?