Extract specific word 'A' only than when that word is mentioned more than 10 times(from first and last name)

Question

I have to get from first name and last name how many times is mentioned the word " A" with the condition that is has to be mention more than 10 times. For example : 1. Alessandra Antatarovich Alatatatovica 2. Aaron Aaaantonich Albachiara .....

Expected result is :

                    `First_name             Last_name            Count`

                     Alessandra Antatarovich Alatatatovica        11
                     Aaron     Aaaantonich   Albachiaraaa         12
                     AAAAcc                 ABCDAAAAAAAAA         14

Name of the table is Client

If there is a name and last name with only 8 " A" than that record will not display.. If there is a record for example First name : AAABBBAAANHDHD and Last name : BBAAAAAAATK that has 13 " A" than this record will appear

@artm, from that example I did't risolve my problem.. I would appreciate if anyone could risolve my problem.. tnx — Goran Bart Bartolić, Mar 09 '16 at 11:06
Can you please post some sample data, the expected result and what you already tried? — Aleksej, Mar 09 '16 at 11:07
@ALEKSEJ, I wanted to use lenght, but i don't have idea how to get just "A" and than count how many times is displayed — Goran Bart Bartolić, Mar 09 '16 at 11:16
@artm, this is not duplicated question. This query have to extract all records that contains more than 10 "A" and than display how many time is showed character "A " — Goran Bart Bartolić, Mar 09 '16 at 11:27
I voted to re-open because there is a better answer than the accepted answer on the "duplicate". — Gordon Linoff, Mar 09 '16 at 11:41

score 2 · Accepted Answer · answered Mar 09 '16 at 11:43

Oracle has the regexp_count() function for this purpose.

The basic syntax is:

select regexp_count(FirstName || LastName, 'A')

For the condition of 10, then something like:

select c.*, regexp_count(FirstName || LastName, 'A')
from client c
where  regexp_count(FirstName || LastName, 'A') > 10;

score 0 · Answer 2 · answered Mar 09 '16 at 11:59

0

and if no rexexp_count() in your SQL - you can use this simple trick:

select *, 
length(concat(First_name,Last_name))- length(substitue(concat(First_name,Last_name),'A','') as 'Acount'
from tableName
having Acount>10

answered Mar 09 '16 at 11:59

Zahiro Mor

1,708
1
16
30

Extract specific word 'A' only than when that word is mentioned more than 10 times(from first and last name)

2 Answers2