OraFAQ Forum: SQL & PL/SQL » Count blank space in a sentences

Home » SQL & PL/SQL » SQL & PL/SQL » Count blank space in a sentences (oracle 10g, sql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Count blank space in a sentences [message #559546]

Tue, 03 July 2012 23:37

swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai

Senior Member

Is There any way to count how many space in the sentences..

Like this

the sentences : " I AM BOY".

There is 2 space in the sentences.

How can i find out using oracle query.

Report message to a moderator

Re: Count blank space in a sentences [message #559547 is a reply to message #559546]

Tue, 03 July 2012 23:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can i find out using oracle query.
remove, eliminate, translate all non-space characters to NULL & the measure the LENGTH of the resultant string.

Report message to a moderator

Re: Count blank space in a sentences [message #559548 is a reply to message #559547]

Wed, 04 July 2012 00:09

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Not once have I cried for the following Oracle 11g function. So, just for the future reference, if you ever upgrade to 11g, have a look how simple it can be:

SQL> with test as
  2    (select ' I am a boy' col from dual)
  3  select regexp_count(col, ' ') result
  4  from test;

    RESULT
----------
         4

SQL>

Report message to a moderator

Re: Count blank space in a sentences [message #559550 is a reply to message #559546]

Wed, 04 July 2012 00:39

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

There is 2 space in the sentences

I think there are 3 in what you posted.
Otherwise you have to clearly specify which spaces you have to count (and which ones have to be ignored).

Regards
Michel

Report message to a moderator

Re: Count blank space in a sentences [message #559686 is a reply to message #559550]

Thu, 05 July 2012 09:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

if you are not comfortable with regular expressions use the following

select len(' I AM BOY') - len(replace(' I AM BOY',' '))
from dual;

Report message to a moderator

Re: Count blank space in a sentences [message #559687 is a reply to message #559686]

Thu, 05 July 2012 09:41

mnitu
Messages: 159
Registered: February 2008
Location: Reims

Senior Member

Bill B wrote on Thu, 05 July 2012 16:30

if you are not comfortable with regular expressions use the following
select len(' I AM BOY') - len(replace(' I AM BOY',' '))
from dual;

you mean length

Report message to a moderator

Re: Count blank space in a sentences [message #559688 is a reply to message #559687]

Thu, 05 July 2012 09:43

Bill B
Messages: 1971
Registered: December 2004

Senior Member

LOL... yes I mean length. I program in about 5 languages. sometimes I get them mixed up sorry.

Report message to a moderator

Re: Count blank space in a sentences [message #559696 is a reply to message #559688]

Thu, 05 July 2012 11:24

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

And, to be generic, you also need NVL:

select length(str) - nvl(length(replace(str,' ')),0)
from some_table;

SY.

[Updated on: Thu, 05 July 2012 11:25]

Report message to a moderator

Previous Topic:	Password for a zip file
Next Topic:	Function and procedure declaration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 27 05:49:16 CDT 2025