SQL: Edit data before join

Question

ORACLE: I am trying to LEFT OUTER JOIN a table, on PART column. First Part Column is formatted as following: "XXXXXXXX". Column number two is formatted as following: "ABCXXXXXXXX". I want to exclude "ABC" before "XXXXXXXX". is there a formula for doing this?

Thank you in advance.

You can use substring. Have a look https://stackoverflow.com/questions/28636051/how-to-join-two-tables-based-on-substring-values-of-fields — Nitin Zadage, Oct 23 '19 at 09:21
What is your actual database (e.g. MySQL, SQL Server, Oracle, etc.)? "SQL" is just a language, not an implementation. — Tim Biegeleisen, Oct 23 '19 at 09:21
Tough luck. You shouldn't have run into this issue of your database was properly designed. — jarlh, Oct 23 '19 at 09:23
I am with jarlh here. If the separate parts of the string are essential for querying the data, then these should be stored separately. You may want to consider fixing the data model, rather than creating queries that deal with it. — Thorsten Kettner, Oct 23 '19 at 09:28

Tim Biegeleisen · Accepted Answer · 2019-10-23T09:31:27.957

0

You could join using the substring on the second column starting with the fourth character, e.g.

SELECT t1.*, t2.*
FROM table1 t1
LEFT JOIN table2 t2
     ON t1.PART = SUBSTR(t2.PART, 4);

edited Oct 23 '19 at 09:31

answered Oct 23 '19 at 09:21

Tim Biegeleisen

502,043
27
286
360

I tried this, but I get error message from Oracle; ORA-00904: "SUBSTRING": invalid identifier – MS_90 Oct 23 '19 at 09:30
@MS_90 You are either using MySQL or Oracle. In either case, use `SUBSTR`, q.v. my updated answer. – Tim Biegeleisen Oct 23 '19 at 09:31

score 0 · Answer 2 · answered Oct 23 '19 at 09:22

0

Try using LIKE, is an operator used to search for a specified pattern in a column.

Try something like this:

column2 LIKE '%' + column1

for mysql you need use concat('%', column1)

It finds any values that end with column1 value

answered Oct 23 '19 at 09:22

i.signori

585
3
16

score 0 · Answer 3 · answered Oct 23 '19 at 11:32

I would recommend:

SELECT t1.*, t2.*
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t2.PART = 'ABC' || t1.PART ;

This allows Oracle to use an index on t2.PART. For an INNER JOIN this would not matter. But in a LEFT JOIN, table1 is going to be scanned anyway. This may allow Oracle to use the an index on table2(PART).

Or more generally:

SELECT t1.*, t2.*
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t2.PART LIKE '___' || t1.PART ;

SQL: Edit data before join

3 Answers3