Reputation: 324
I need to join two tables but the strings I want to join on are not obvious matches. Here are the tricky columns (let's call them both 'col') from two tables (Note that there's a lot more in these columns but I'm only interested in entries which contain the word "Campaign"):
TableA TableB
Campaign eCom Q2 2016 PH_Campaign Q2_042016
Campaign eCom Q3 2016 PH_Campaign Q3 ecomm_072016
Campaign Q1 2017 (AUTH 380) PH_Campaign_Q1 2017_01012017
Campaign Q2 2017 (AUTH 421) PH_Campaign_Q2_042017
Campaign Q3 2017 (AUTH 451) Campaign_Q3_072017
Campaign Q4 2016 PH_Campaign Q4_102016
Campaign: Retail Stores PH_Campaign Retail_092016
I've written them so the entries on each row should match together
I want to write a query like this
SELECT *
FROM TableA A INNER JOIN TableB B
ON A."date" = B."date" AND A.col LIKE B.col
That's where I get stuck... Is this a case for regex or can I do this with a LIKE statement? Each row should contain the word Campaign, the quarter, and the year, so for the first row I'd match on Contains: "Campaign", "Q2", and "2016". Then there's the last row which should match on Contains: "Campaign", "Retail".
How do I do this?
Upvotes: 0
Views: 2647
Reputation: 1693
You stated that the column had other information in there but without seeing the other data you might have to tweak the regex strings. But hopefully you get the idea.
WITH cte_a
AS (
SELECT col
,substring(col FROM '%#"Campaign#"%' FOR '#') AS Campaign
,substring(col FROM '%#"Q[1234]#"%' FOR '#') AS QTR
,substring(col FROM '%#"20[0-9][0-9]#"%' FOR '#') AS YEAR
FROM table_a
)
,cte_b
AS (
SELECT col
,substring(col FROM '%#"Campaign#"%' FOR '#') AS Campaign
,substring(col FROM '%#"Q[1234]#"%' FOR '#') AS QTR
,substring(col FROM '%#"20[0-9][0-9]#"%' FOR '#') AS YEAR
FROM table_b
)
SELECT *
FROM cte_a
INNER JOIN cte_b ON (
cte_a.Campaign = cte_b.Campaign
AND cte_a.qTR = cte_b.QTR
AND cte_a.YEAR = cte_b.YEAR
)
HTH,
Upvotes: 2