title: REGEXP_COUNT in postgres
body: <p>We are migrating from Oracle to Postgres. </p>
  
  <p>here is SQL, where i used to extract data from employee_name column and used to report. </p>
  
  <p>but now i am not sure how to do the regex_count part. 
  Oracle SQL</p>
  
  <pre><code>with A4 as 
  (
  select 'govinda j/INDIA_MH/9975215025' as employee_name from dual
  )
  select employee_name , 
  TRIM(SUBSTR(upper(A4.employee_name),1,INSTR(A4.employee_name,'/',1,1)-1)) AS employee_name1,
    TRIM(SUBSTR(upper(A4.employee_name),INSTR(A4.employee_name,'/',1,1)+1,INSTR(A4.employee_name,'_',1,1)-INSTR(A4.employee_name,'/',1,1)-1)) AS Country,
    TRIM(SUBSTR(upper(A4.employee_name),INSTR(A4.employee_name,'_',1,1)+1,INSTR(A4.employee_name,'/',1,2)-INSTR(A4.employee_name,'_',1,1)-1)) AS STATE,
    CASE WHEN REGEXP_COUNT(A4.employee_name,'_')&gt;1 THEN 'WRONG_NAME&gt;1_'
         WHEN REGEXP_COUNT(A4.employee_name,'/')&gt;2 THEN 'WRONG_NAME&gt;2/'
         WHEN TRIM(SUBSTR(upper(A4.employee_name),INSTR(A4.employee_name,'/',1,1)+1,INSTR(A4.employee_name,'_',1,1)-INSTR(A4.employee_name,'/',1,1)-1))NOT IN
           ('INDIA','NEPAL') THEN 'WRONG_COUNTRY'
         ELSE 'CORRECT' END AS VALIDATION
  
         from A4
  </code></pre>
  
  <p>In Postgres with help i am able to convert it into below part. </p>
  
  <pre><code>with A4 as 
  (
  select 'govinda j/INDIA_MH/9975215025'::text as employee_name
  )
  select employee_name,
         split_part(employee_name, '/', 1) as employee_name1,
         split_part(split_part(employee_name, '/', 2), '_', 1) as country,
         split_part(split_part(employee_name, '/', 2), '_', 2) as state
  from A4
  </code></pre>
  
  <p>But <strong>validation</strong> part in not able to convert . any help is highly appreciated as we are very new to postgres. </p>