oracle DB - joining with a messy string

user_926178 0
Viewed 55

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 a best effort match. The BUG_ID will ALWAYS be 7 numeric digits and the URL in Table 1 is a string, but I am happy to only use "valid" strings which is a URL ending in:

https://example.com/show_bug.cgi?id=1958615 <-- this is the BUG_ID
oracle sql regex
2 Answers

How about:

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

In case you are looking for url, say

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

you can try regular expression, 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)
user_23194070
Related