title: Dynamic regex search string from another table
body: <p>I have two tables:</p>
  
  <p><strong>items</strong></p>
  
  <pre><code>CREATE TABLE items (
    ID int,
    TXT string,
    CODE string
  );
  
  INSERT INTO items VALUES (1,'AA BB CC','ZZ-100');
  INSERT INTO items VALUES (2,'BB CC DD','ZZ-200');
  INSERT INTO items VALUES (3,'AA CC EE','ZZ-300');
  INSERT INTO items VALUES (4,'EE FF GG','ZZ-400');
  INSERT INTO items VALUES (5,'CC HH II','ZZ-500');
  
  +----+----------+--------+
  | id | txt      | code   |
  +----+----------+--------+
  | 1  | AA BB CC | ZZ-100 |
  | 2  | BB CC DD | ZZ-200 |
  | 3  | AA CC EE | ZZ-300 |
  | 4  | EE FF GG | ZZ-400 |
  | 5  | CC HH II | ZZ-500 |
  +----+----------+--------+
  </code></pre>
  
  <p>And <strong>regex_table</strong>:</p>
  
  <pre><code>CREATE TABLE regex_table (
    ID int,
    REGEXSTR string,
    CODE string
  );
  
  INSERT INTO regex_table VALUES(1,'AA','ZZ-100');
  INSERT INTO regex_table VALUES(1,'CC','ZZ-100');
  INSERT INTO regex_table VALUES(2,'AA','ZZ-100');
  INSERT INTO regex_table VALUES(2,'BB','ZZ-200');
  INSERT INTO regex_table VALUES(2,'CC','ZZ-200');
  INSERT INTO regex_table VALUES(3,'DD','ZZ-100');
  INSERT INTO regex_table VALUES(3,'DD','ZZ-300');
  
  +----+----------+--------+
  | id | regexstr | code   |
  +----+----------+--------+
  | 1  | AA       | ZZ-100 |
  | 1  | CC       | ZZ-100 |
  | 2  | BB       | ZZ-200 |
  | 2  | AA       | ZZ-100 |
  | 2  | CC       | ZZ-200 |
  | 3  | DD       | ZZ-100 |
  | 3  | DD       | ZZ-300 |
  +----+----------+--------+
  </code></pre>
  
  <p>I would like to replace <code>items.txt</code> with search string in <code>regex_table.regexstr</code> depending on if the <code>id</code> and <code>code</code> are equal.</p>
  
  <p>For example:</p>
  
  <p><strong>Scenario 1:</strong> If <code>id=1</code>, the <code>code</code> is <code>ZZ-100</code>, therefore the search string is <code>AA|CC</code>:</p>
  
  <pre><code>SELECT id,regexp_replace(txt,'AA|CC','&lt;NA&gt;'),code from items where id=1;
  
  +----+--------------------------------------+--------+
  | id | regexp_replace(txt, 'aa|cc', '&lt;na&gt;') | code   |
  +----+--------------------------------------+--------+
  | 1  | &lt;NA&gt; BB &lt;NA&gt;                         | ZZ-100 |
  +----+--------------------------------------+--------+
  </code></pre>
  
  <p><strong>Scenario 2:</strong> If <code>id=2</code>, the <code>code</code> is <code>ZZ-200</code>, therefore the search string is <code>BB|CC</code>:</p>
  
  <pre><code>SELECT id,regexp_replace(txt,'BB|CC','&lt;NA&gt;'),code from items where id=2;
  
  +----+--------------------------------------+--------+
  | id | regexp_replace(txt, 'bb|cc', '&lt;na&gt;') | code   |
  +----+--------------------------------------+--------+
  | 2  | &lt;NA&gt; &lt;NA&gt; DD                         | ZZ-200 |
  +----+--------------------------------------+--------+
  </code></pre>
  
  <p><strong>Scenario 3:</strong> If <code>id=4</code>, the <code>code</code> is <code>ZZ-300</code>, therefore the search string is <code>DD</code>:</p>
  
  <pre><code>SELECT id,regexp_replace(txt,'DD','&lt;NA&gt;'),code from items where id=3;
  
  +----+-----------------------------------+--------+
  | id | regexp_replace(txt, 'dd', '&lt;na&gt;') | code   |
  +----+-----------------------------------+--------+
  | 3  | AA CC EE                          | ZZ-300 |
  +----+-----------------------------------+--------+
  </code></pre>
  
  <p>So basically the search string has to be dynamic depending on <code>id</code> and <code>code</code> from another table.</p>
  
  <p><strong>Is there a way to do this in one query either in Impala (important) and Hive (less important)?</strong></p>
  
  <p><strong>NOTE</strong>:</p>
  
  <ol>
  <li><p>The <code>id</code> and <code>code</code> could be dynamic and added in both tables (so no way to hardcode into SQL). It has to be queried.</p></li>
  <li><p>I try to avoid doing <code>JOIN</code>. I am wondering if there is a way to do subquery.</p></li>
  <li><p>One idea is to pass a full string that contains concat Regex search string and then use some Regex tricks to remove 'id' and 'code' not relevant to the row.</p></li>
  </ol>
  
  <p><strong>UPDATE 1</strong></p>
  
  <p>I tried this:</p>
  
  <pre><code>SELECT i.id, regexp_replace(txt, pattern, '&lt;NA&gt;'), i.code FROM items i INNER JOIN (SELECT id, group_concat('|', regexstr) AS pattern, regex_table.code FROM regex_table GROUP BY regex_table.id, regex_table.code) r ON r.id = i.id AND r.code = i.code;
  </code></pre>
  
  <p>And got this:</p>
  
  <pre><code>+----+----------------------------------------------+--------+
  | id | regexp_replace(txt, pattern, '&lt;na&gt;')         | code   |
  +----+----------------------------------------------+--------+
  | 1  | &lt;NA&gt;A&lt;NA&gt;A&lt;NA&gt; &lt;NA&gt;B&lt;NA&gt;B&lt;NA&gt; &lt;NA&gt;           | ZZ-100 |
  | 3  | &lt;NA&gt;A&lt;NA&gt;A&lt;NA&gt; &lt;NA&gt;C&lt;NA&gt;C&lt;NA&gt; &lt;NA&gt;E&lt;NA&gt;E&lt;NA&gt; | ZZ-300 |
  | 2  | &lt;NA&gt;B&lt;NA&gt;B&lt;NA&gt; &lt;NA&gt; &lt;NA&gt;D&lt;NA&gt;D&lt;NA&gt;           | ZZ-200 |
  +----+----------------------------------------------+--------+
  </code></pre>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>I got it to work</p>
  
  <pre><code>SELECT o.id, 
         o.code, 
         items.txt, 
         o.regexstr, 
         IF(o.regexstr IS NOT NULL, regexp_replace(items.txt, o.regexstr, 
         '&lt;NA&gt;'), items.txt) masked 
  FROM   items 
         LEFT JOIN (SELECT i.id                          id, 
                           i.code                        code, 
                           group_concat(r.regexstr, '|') regexstr 
                    FROM   items i 
                           left join (SELECT id, 
                                             regexstr, 
                                             regex_table.code 
                                      FROM   regex_table) r 
                                  ON r.id = i.id 
                                     AND r.code = i.code 
                    GROUP  BY i.id, 
                              i.code) o 
                ON items.id = o.id 
                   AND items.code = o.code; 
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>+----+--------+----------+----------+--------------+
  | id | code   | txt      | regexstr | masked       |
  +----+--------+----------+----------+--------------+
  | 5  | ZZ-500 | CC HH II | NULL     | CC HH II     |
  | 2  | ZZ-200 | BB CC DD | BB|CC    | &lt;NA&gt; &lt;NA&gt; DD |
  | 4  | ZZ-400 | EE FF GG | NULL     | EE FF GG     |
  | 3  | ZZ-300 | AA CC EE | DD       | AA CC EE     |
  | 1  | ZZ-100 | AA BB CC | CC|AA    | &lt;NA&gt; BB &lt;NA&gt; |
  +----+--------+----------+----------+--------------+
  </code></pre>
  
  <p>But it seems rather "complicated". Any idea to make it more concise?</p>