Rqomey Rqomey - 27 days ago 7
SQL Question

oracle DB - joining with a messy string

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

Answer Source

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)