How to extract a substring as a new column using Impala SQL

I want to extract names from two columns in one table and join them to another table. If the name in the "name" column is "NOT FOUND", I would like to extract it from the "Description" column.

In the "Description" column, 3 patterns will follow:

• Name not found. Name: ab33c and customer

  So, in this case, I want to extract ab33c.

• The name j2fc_being not found: j2fc_being_decom_2017

  I want to extract j2fc.

• Name w3fkk not found: Resume:

  I want to extract w3fkk.

Below are the codes I am writing:

SELECT inc.name, inc.Description, inc.new_name, srv.dv_category, srv.virtual,
FROM ( 
SELECT inc.name, inc.Description, 
CASE 
  WHEN inc.name NOT LIKE 'NOT FOUND%' THEN inc.name
  WHEN inc.Description LIKE '%Name :%' THEN REGEXP_REPLACE(inc.Description, '.*Name :(.+)(\s).*','\1')
  WHEN inc.Description LIKE 'Name%being%' THEN REGEXP_REPLACE(inc.Description, '.*(\s)(.+)_.+','\1') 
  WHEN inc.Description LIKE 'Name%was%' THEN REGEXP_REPLACE(inc.Description, '.*(\s)(.+)(\s).+','\1') 
  ELSE inc.name
  END as new_name
FROM incident inc
) inc
LEFT JOIN server srv 
ON inc.new_name = srv.dv_name

      

        
        
        
      

    

The context is Impala SQL. Can I get help retrieving it? Thank you very much.