CODEWITHSUNDEEP
sqlpostgresql
Greg
Greg

Reputation: 324

Join two tables in PostgreSQL on partial string matches

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

Answers (1)

Ed Mendez
Ed Mendez

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

Related Questions