Joining two tables where the join condition requires a substring

2
votes

I am trying to join two oracle database tables where the columns to join on contain slightly different data.

For example Table A has a column 'ref' and Table B has a column 'id'.

A.ref contains data like A1234567890B and B.id contains data of the form 1234567890

I have tried joining the two based on the following query;

SELECT * FROM A INNER JOIN B
ON SUBSTR(A.ref, 2,10) = B.id;

But this has returned no results when I know that there is matching data from this substring.

Any ideas?

2
sqloraclejoin
post your sqlfiddle.com - Naveen Kumar Alone
Query looks good. Try looking at what substr returns. Don't forget that it is 1-based. - Thilo
What are the column types? Fixed-length CHAR fields sometimes "misbehave". - Thilo
Given your examples (A1234567890B and 123456789) the query works as expected. What are the data types of each column? - GarethD
Either use varchar2 or (if you can't change the datatypes) use trim as in this example sqlfiddle.com/#!4/04849/2 - A.B.Cade

2 Answers

0
votes

you could try something like this:

SELECT * FROM A INNER JOIN B ON regexp_substr(A.ref, '^[[:alpha:]]+([[:digit:]]+)[[:alpha:]]+$',1,1,'c',1) = B.id

0
votes

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