Search code examples
oracle-databasejoinmatchdigits

Oracle joining tables where the matching process starts after certain digits

I have joined two tables which contain words as well as values. The matching of the words works. The matching of the values do not. The reason is that the values in the table "GATTUNGSDATEN" always start with 4 zeros whereas in the table BONDMAPPING not. E.g. 00001234 = 1234. What query could I use in order to have the matching process started after 4 digits?

select MET_SEC_INDEX_GATTUNGSDATEN.ranl, MET_SEC_INDEX_GATTUNGSDATEN.vvranlwx, 
V_AR_BONDMAPPING.MUREXEXTERNALID

from V_AR_BONDMAPPING

RIGHT JOIN MET_SEC_INDEX_GATTUNGSDATEN

ON V_AR_BONDMAPPING.MUREXEXTERNALID = MET_SEC_INDEX_GATTUNGSDATEN.ranl IN 
('%%%%')

where vvranlwi=34
Solution

  • If it's always four leading zeroes you can use SUBSTR():

    from V_AR_BONDMAPPING
RIGHT JOIN MET_SEC_INDEX_GATTUNGSDATEN
    ON V_AR_BONDMAPPING.MUREXEXTERNALID =  substr(MET_SEC_INDEX_GATTUNGSDATEN.ranl, 5)

    This trims the leading four characters from MET_SEC_INDEX_GATTUNGSDATEN.ranl