Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nitesh.

Asked: July 26, 2016 - 4:43 pm UTC

Last updated: July 27, 2016 - 5:22 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Sir,
I want the query so that I have the names of person who are having exactly have 2 'a' in his or her name..

M trying query but m getting more then 2 'a'.
But i want exactly two.

Thanks.

and Chris said...

Here's a couple of methods:

- Use regexp_count to find the number of times "a" occurs
- Subtract the length of the name with "a" removed from the its total length. This gives you the number of times "a" appears in the name:

with names as (
  select 'Chris' n from dual union all
  select 'James' n from dual union all
  select 'Sarah' n from dual union all
  select 'Samantha' n from dual 
)
  select n from names
  where  regexp_count(n, 'a') = 2;

N      
Sarah 
 
with names as (
  select 'Chris' n from dual union all
  select 'James' n from dual union all
  select 'Sarah' n from dual union all
  select 'Samantha' n from dual 
)
  select n from names
  where  length(n) - length(replace(n, 'a')) = 2;

N      
Sarah

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

check once

Nitesh, July 27, 2016 - 10:15 am UTC

Sir,
My table contains "name" column in which entries are like :
Nitesh
Ajay
Tom
Thatharao
IZmandanish
vallamalareddy
etc
now i Want the the name of person who are having exactly two times the occurance of letter 'a'.i.e. izmandanish.


Chris Saxon
July 27, 2016 - 5:21 pm UTC

As the review below says, just replace the with clause with you table name!

Thats what they gave you, just not with a table.

paul, July 27, 2016 - 1:55 pm UTC

Maybe this is more clear?

drop table t;
create table t (name varchar2(60));
insert into t values ('Nitesh');
insert into t values ('Ajay'); 
insert into t values ('Tom'); 
insert into t values ('Thatharao'); 
insert into t values ('IZmandanish'); 
insert into t values ('vallamalareddy');

//looks for 'a' ONLY
 select name from t
  where  length(name) - length(replace(name, 'a')) = 2;

//looks for 'a' ONLY 
   select name from t
  where  regexp_count(name, 'a') = 2;

//Loos for 'a' and 'A' (case insensitive)
   select name from t
  where  regexp_count(name, 'a',1,'i') = 2;



Chris Saxon
July 27, 2016 - 5:22 pm UTC

Thanks for clarifying
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.