title: How to extract domain name using dynamic regex in Redshift?
body: <p>I need to extract domain name from url using Redshift PostgreSQL. Example : extract 'google.com' from 'www.google.com'. Each url in my dataset has different top level domain (TLD). My approach was to first join the matching TLD to the dataset and use regex to extract 'first_string.TLD'. In Redshift, I'm getting error 'The pattern must be a valid UTF-8 literal character expression'. Is there a way around this?</p>
  
  <p>A sample of my dataset:</p>
  
  <pre><code> +---+------------------------+--------------+
   | id|    trimmed_domain      |  tld         |
   +---+------------------------+--------------+
   | 1 | sample.co.uk           | co.uk        |
   | 2 | www.sample.co.uk       | co.uk        |
   | 3 | www3.sample.co.uk      | co.uk        |
   | 4 | biz.sample.co.uk       | co.uk        |
   | 5 | digital.testing.sam.co | co           |
   | 6 | sam.co                 | co           |
   | 7 | www.google.com         | com          |
   | 8 | 1.11.220               |              |
   +---+------------------------+--------------+ 
  </code></pre>
  
  <p>My code:</p>
  
  <pre><code> SELECT t1.extracted_domain, COUNT(DISTINCT(t1.id))
   FROM(
       SELECT 
       d.id,
       d.trimmed_domain,
       CASE 
       WHEN d.tld IS null THEN d.trimmed_domain ELSE 
        regexp_replace(d.trimmed_domain,'(.*\.)((.[a-z]*).*'||replace(tld,'.','\.')||')','\2') 
       END AS "extracted_domain" 
       FROM dataset d
       )t1
    GROUP BY 1
    ORDER BY 2;
  </code></pre>
  
  <p>Expected output:</p>
  
  <pre><code> +------------------------+--------------+
   |    extracted_domain    |  count       |
   +------------------------+--------------+
   | sample.co.uk           | 4            |
   | sam.co                 | 2            |
   | google.com             | 1            |
   | 1.11.220               | 1            |
   +------------------------+--------------+
  </code></pre>