usage of LIKE, compare 2 fields.

[Ahmad Sakhi]

Hello All,

Considering the following 2 tables:

TABLE_1
ID    URI_EXT
12    IMO0001_0001_59909
13    IMO0002_0002_60109
14    IMJ00001_00059_000292
...

TABLE_2
ID    URI
01    IMO0001
02    IMO0002
03    IMJ00001
...

I need to join these 2 tables using the URI and URI_EXT field. But, as we can see in the first table the URI_EXT has extra information. I need to use a query something like the following,

SELECT 
    a.id, 
    a.uri_ext, 
    b.id, 
    b.uri 
FROM 
    table_1 a, 
    table_2 b 
WHERE 
    a.uri_ext like b.uri AND
    b.uri='IMJ00001';

Is there a way, I can use the SUBSTRING() function to parse the uri_ext field where the first occurance of _ (underscore) is and do a compare of the two field? i.e.

WHERE SUBSTRING(a.uri_ext, 0, '-') = b.uri

Or there maybe another way?