REGEXP_COUNT in postgres

Question

We are migrating from Oracle to Postgres.

here is SQL, where i used to extract data from employee_name column and used to report.

but now i am not sure how to do the regex_count part. Oracle SQL

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,'_')>1 THEN 'WRONG_NAME>1_'
       WHEN REGEXP_COUNT(A4.employee_name,'/')>2 THEN 'WRONG_NAME>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

In Postgres with help i am able to convert it into below part.

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

But validation part in not able to convert . any help is highly appreciated as we are very new to postgres.

Answer 1

You can create a custom function:

create or replace function number_of_chars(text, text)
returns integer language sql immutable as $$
    select length($1) - length(replace($1, $2, ''))
$$; 

Use:

with example(str) as (
values 
    ('a_b_c'),
    ('a___b'),
    ('abc')
)

select str, number_of_chars(str, '_') as count
from example

  str  | count  
-------+-------
 a_b_c |     2
 a___b |     3
 abc   |     0
(3 rows)

Note that the above function just counts occurrences of a character in a string and does not use regular expressions, which in general are more expensive.

A Postgres equivalent of regexp_count() may look like this:

create or replace function regexp_count(text, text)
returns integer language sql as $$
    select count(m)::int
    from regexp_matches($1, $2, 'g') m
$$; 

with example(str) as (
values 
    ('a_b_c'),
    ('a___b'),
    ('abc')
)

select str, regexp_count(str, '_') as single, regexp_count(str, '__') as double
from example

  str  | single | double 
-------+--------+--------
 a_b_c |      2 |      0
 a___b |      3 |      1
 abc   |      0 |      0
(3 rows)