title: Using regexp_extract in Hive
body: <p>I am trying to find the rows from a hive table where a particular column does not contain null values or \N values or STX character '\002'. The objective is to find which rows contain some characters other than these three.</p>
  
  <p>I tried this hive query:</p>
  
  <pre><code>    select column1,length(regexp_replace(column1,'\N|\002|NULL','')) as value         
      FROM table1 LIMIT 10;
  </code></pre>
  
  <p>I was expecting zero in the following cases but I am getting the following:</p>
  
  <pre><code>    column1 value
      NULL    NULL
              0
      NULL    NULL
              0
      \N\N\N\N\N\N\N\N        8
      NULL    NULL
      \N\N\N\N\N\N\N\N        8
      NULL    NULL
      NULL    NULL
      \N\N\N  3
  </code></pre>
  
  <p>Could someone please help me on the correct regex for the above case?</p>
  
  <p>Thank you.</p>
  
  <p>Ravi</p>