Oracle DB - dirty string join

Question

I am trying to join two tables in oracle:

Table 1

Jira ID|bugz url(string)
-------|---------
1234   |http-url-897654
1235   |http-url-158974
1236   |http-url-158975\nhttp-url-158972

table 2

BUG_ID(int)|Stuff
-------|---------
897654 |Stuff
158974 |Stuff
158975 |Stuff

I want to join table 2 to table 1 using the best combination of efforts. BUG_ID will ALWAYS be 7 numeric digits and the URL in table 1 is a string, but I am glad to use only the "correct" strings, which are a URL ending in:

https://example.com/show_bug.cgi?id=1958615 <-- this is the BUG_ID

Answer 1

If you are looking for a url, say

  http(s): ... ?id=7_DIGITS_BUG_NUMBER

you can try the regexp regexp_substr in case of Oracle:

   select ...
     from table1 join table2
       on regexp_substr(table1.bugz_url, '^https?:.*\?id=([0-9]{7})$', 1, 1, null, 1) =
           to_char(table2.bug_id)

Answer 2

What about:

select ...
  from table1
       join table2
            on substr(table1.bugz_url, -7) = to_char(table2.bug_id);