title: Join 2 tables in Hive using a phone number and a prefix (variable length)
body: <p>I'm trying to match phone numbers to an area using Hive.
  I've got a table (prefmap) that maps a number prefix (prefix) to an area (area) and another table (users) with a list of phone numbers (nb).
  There is only 1 match per phone number (no sub-area)</p>
  
  <p>The problem is that the length of the prefixes is not fixed so I cannot use the UDF function substr(nb,"prefix's length") in the JOIN's ON() condition to match the substring of a number to a prefix.</p>
  
  <p>And when I try to use instr() to find if a number has a matching prefix:</p>
  
  <pre><code>SELECT users.nb,prefix.area
  FROM users 
  LEFT OUTER JOIN prefix 
  ON (instr(prefmap.prefix,users.nb)=1)
  </code></pre>
  
  <p>I get an error on line4 "Both left and right aliases encountered in Join '1')</p>
  
  <p>How could I get this to work?
  I'm using hive 0.9
  Thanks for any advice.</p>