title: Extracting strings between distinct characters using hive SQL
body: <p>I have a field called geo_data_display which contains country, region and dma. The 3 values are contained between = and &amp; characters - country between the first "=" and the first "&amp;", region between the second "=" and the second "&amp;" and DMA between the third "=" and the third "&amp;". Here's a re-producible version of the table. country is always character but region and DMA can be either numeric or character and DMA doesn't exist for all countries.</p>
  
  <p>A few sample values are:</p>
  
  <pre><code>country=us&amp;region=tx&amp;dma=625&amp;domain=abc.net&amp;zipcodes=76549
  country=us&amp;region=ca&amp;dma=803&amp;domain=abc.com&amp;zipcodes=90404 
  country=tw&amp;region=hsz&amp;domain=hinet.net&amp;zipcodes=300
  country=jp&amp;region=1&amp;dma=a&amp;domain=hinet.net&amp;zipcodes=300  
  </code></pre>
  
  <p>I have some sample SQL but the geo_dma code line isn't working at all and the geo_region code line only works for character values</p>
  
  <pre><code>SELECT 
  
  UPPER(REGEXP_REPLACE(split(geo_data_display, '\\&amp;')[0], 'country=', '')) AS geo_country
  ,UPPER(split(split(geo_data_display, '\\&amp;')[1],'\\=')[1]) AS geo_region
  ,split(split(cast(geo_data_display as int), '\\&amp;')[2],'\\=')[2] AS geo_dma
  FROM mytable
  </code></pre>