title: Select specific value from Hive array
body: <p>I have a table in Hive structured with 3 columns as follows;</p>
  <pre><code>timestamp   UserID  OtherId    
  2016-09-01  123     &quot;101&quot;,&quot;222&quot;,&quot;321&quot;,&quot;987&quot;,&quot;393.1&quot;,&quot;090&quot;,&quot;467&quot;,&quot;863&quot;
  2016-09-01  124     &quot;188&quot;,&quot;389&quot;,&quot;673&quot;,&quot;972&quot;,&quot;193&quot;,&quot;100&quot;,&quot;143&quot;,&quot;210&quot;
  2016-09-01  125     &quot;888&quot;,&quot;120&quot;,&quot;482&quot;,&quot;594&quot;,&quot;393.2&quot;
  2016-09-01  126     &quot;441&quot;,&quot;501&quot;,&quot;322&quot;,&quot;671&quot;,&quot;008&quot;,&quot;899&quot;
  2016-09-01  127     &quot;004&quot;,&quot;700&quot;,&quot;393.4&quot;,&quot;761&quot;,&quot;467&quot;,&quot;356&quot;,&quot;643&quot;,&quot;578&quot;
  2016-09-01  128     &quot;322&quot;,&quot;582&quot;,&quot;348&quot;
  2016-09-01  129     &quot;029&quot;,&quot;393.8&quot;,&quot;126&quot;,&quot;187&quot;
  </code></pre>
  <p>Where OtherID is an array.</p>
  <p>I need to parse OtherID so that the resultant dataset is the following, since I am only interested in values which contain '393%'</p>
  <pre><code>timestamp   UserID  OtherId    
  2016-09-01  123     393.1
  2016-09-01  125     393.2
  2016-09-01  127     393.4
  2016-09-01  129     393.8
  </code></pre>
  <p>I have researched a ton of parse functions but it seems they're all intended to return the position of the value, or you need to specify the position of the value to return it. Both of these options do not work here because '3309%' can occur at any point in the array for any given row.
  There's also the fact that I need to incorporate the wildcard to allow for variations of my desired value.</p>
  <p>Another option is explode but my table is simply too large for that option.</p>
  <p>I'm thinking a UDF might be the only way to go but would welcome some guidance there.</p>
  <p>Grateful for any assistance.</p>