Home » SQL & PL/SQL » SQL & PL/SQL » How many 'a' we have before 's'
How many 'a' we have before 's' [message #204812] Wed, 22 November 2006 04:03 Go to next message
Messages: 23
Registered: November 2006
Location: India
Junior Member


I have a string like : 'as simple as possible'.

I want to find out how many 'a' we have before 's'.

For that how I can write the query.

Please help me.

Thanks & Regards,

Re: How many 'a' we have before 's' [message #204815 is a reply to message #204812] Wed, 22 November 2006 04:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Not much difference

other post

select (length('astast astas') - length(replace('astast astas','as','')))/2 from dual;


[Updated on: Wed, 22 November 2006 04:13]

Report message to a moderator

Re: How many 'a' we have before 's' [message #204820 is a reply to message #204812] Wed, 22 November 2006 04:29 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interesting. You read the problem in a different way to me. I read it as
How many occurrences of the letter 'a' are there before the first 's'
and you read it as
how many occurrences are there of the letter 'a' immediately followed by a letter 's'

I wonder who's right?
Here's a solution to the way I read it:
select length(substring) - length(replace(substring,'a',null)) 
from  (select substr('as simple as possible',1,instr('as simple as possible','s')) substring
       from   dual);
Previous Topic: HELP ASAP
Next Topic: trigger - one user to another user
Goto Forum:

Current Time: Thu Mar 23 07:24:16 CDT 2017

Total time taken to generate the page: 0.10134 seconds