how count results from a multiple regexp oracle

Question

I am having to do a regexp_like and list over 130 different checks.

at the moment I am getting all the fields in the table that do match.

but in the results table I just want a list of the searches and a count next to them.

below is

SELECT *
FROM company
WHERE REGEXP_LIKE (company_name, '(*)A. en P.$')
or REGEXP_LIKE (company_name, '(*)A.C.$')
or REGEXP_LIKE (company_name, '(*)GmbH$')
or REGEXP_LIKE (company_name, '(*)A/S$')

the results I would like to see is

LegalExpression | Count

These are just 4 expressions I have 130 to search

score 1 · Answer 1 · answered Jan 22 '18 at 16:07

You could use UNION ALL:

SELECT '()A. en P.$' as reg, COUNT(*) AS cnt
FROM company 
WHERE REGEXP_LIKE (company_name, '()A. en P.$')
UNION ALL 
SELECT '()A.C.$' as reg, COUNT(*) AS cnt
FROM company 
WHERE REGEXP_LIKE (company_name, '()A.C.$') 
...

Another way:

WITH cte(reg) AS (
   SELECT '()A. en P.$' FROM dual UNION ALL
   SELECT '()A.C.$' FROM dual
   -- ...
)
SELECT cte.reg, COUNT(c.company_name) AS cnt
FROM company c
RIGHT JOIN cte
  ON  REGEXP_LIKE (c.company_name, cte.reg)
GROUP BY cte.reg
;

I tried the first method on your post. it worked and was slow. I am just wondering if I did use it and replicate for 130 expressions, would it the server? — FaisalH, Jan 22 '18 at 17:04

Gordon Linoff · Answer 2 · 2018-01-23T02:06:18.633

If you want a company to count for only one regular expression, you can do:

select (case when REGEXP_LIKE(company_name, '(*)A. en P.$') then '(*)A. en P.$'
             when REGEXP_LIKE(company_name, '(*)A.C.$') then '(*)A.C.$'
             when REGEXP_LIKE(company_name, '(*)GmbH$') then '(*)GmbH$'
             when REGEXP_LIKE(company_name, '(*)A/S$') then '(*)A/S$'
        end), count(*)
from company
group by (case when REGEXP_LIKE(company_name, '(*)A. en P.$') then '(*)A. en P.$'
               when REGEXP_LIKE(company_name, '(*)A.C.$') then '(*)A.C.$'
               when REGEXP_LIKE(company_name, '(*)GmbH$') then '(*)GmbH$'
               when REGEXP_LIKE(company_name, '(*)A/S$') then '(*)A/S$'
        end);

If you want to count a company for each match, then this is one way:

with patterns(pattern) as (
      select '(*)A. en P.$' from dual union all
      select '(*)A.C.$' from dual union all
      select '(*)GmbH$' from dual union all
      select '(*)A/S$' from dual
     )
select p.pattern, count(*)
from company c join
     patterns p
     on regexp_like(c.company_name, p.pattern)
group by p.pattern;

Some notes about your patterns:

I am not sure what (*) is. You don't need anything at the beginning because regular expressions find the pattern in the string; they don't start at the beginning of the string.
. is a special character in regular expressions that is basically any single character.

Hi I tried the first one but comes back with this error. ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: Error at Line: 4 Column: 69 — FaisalH, Jan 22 '18 at 17:02

how count results from a multiple regexp oracle

2 Answers2