title: Extracting field name value from a long | separated string in Cloudera Impala using REGEXP_EXTRACT
body: <p>I am new to REGEXP and its syntax. </p>
  
  <p>I have a long string of multiple pairs in the format field_name:value separated by |
  Using Impala Shell, I am trying run a SQL Select to search for a particular field_name: and then need to get whatever is in value till the next |</p>
  
  <p>Tried to use this guide, but it does not give full syntax for POSIX
  <a href="https://www.cloudera.com/documentation/enterprise/5-9-x/topics/impala_string_functions.html" rel="nofollow noreferrer">https://www.cloudera.com/documentation/enterprise/5-9-x/topics/impala_string_functions.html</a></p>
  
  <p>I tried this</p>
  
  <pre><code>SELECT 
  CASE
      WHEN LONGSTRING IS NOT NULL
      AND REGEXP_LIKE(LONGSTRING, 'FIELD_NAME1:') = true
      THEN REGEXP_EXTRACT(LONGSTRING, 'FIELD_NAME1:([^\|]+\|',1)
  END AS LONGSTRING_PARSED
  from Table1
  </code></pre>
  
  <p>I tried above after reading a bit on REGEX101 and reading this prior post
  <a href="https://stackoverflow.com/questions/51100923/regexp-extract-in-impala">REGEXP_EXTRACT in Impala</a></p>
  
  <p>Sample long string
  FIELD_NAME1:VALUE1|FIELD_NAME2:VALUE2|FIELD_NAME3:VALUE3</p>
  
  <p>Sample SQL I tried</p>
  
  <pre><code>SELECT 
  CASE
    WHEN LONGSTRING IS NOT NULL
    AND REGEXP_LIKE(LONGSTRING, 'FIELD_NAME1:') = true
    THEN REGEXP_EXTRACT(LONGSTRING, 'FIELD_NAME1:([^\|]+\|',1)
  END AS LONGSTRING_PARSED
  from Table1
  </code></pre>
  
  <p>Expecting to see
  VALUE1</p>
  
  <p>Actual Result
  Null</p>