Home » SQL & PL/SQL » SQL & PL/SQL » Find the spaces in a word
Find the spaces in a word [message #241523] Tue, 29 May 2007 21:35 Go to next message
mohankumar
Messages: 8
Registered: May 2007
Junior Member
Hi,

I am trying to find the number of spaces in a word, it's little bit urgent Please help me... For example following word has 4 spaces in a word. I was trying to find with Substr & Instr but I didn't get the right results...

ex: Forum where people can ask.

Regards,
Mohan

Re: Find the spaces in a word [message #241524 is a reply to message #241523] Tue, 29 May 2007 21:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> it's little bit urgent Please help me
When is this homework assignment due?
Must the solution only use SQL & not use PL/SQL?
Re: Find the spaces in a word [message #241526 is a reply to message #241524] Tue, 29 May 2007 21:48 Go to previous messageGo to next message
mohankumar
Messages: 8
Registered: May 2007
Junior Member
I have to complete by the end of tommrow..
Re: Find the spaces in a word [message #241528 is a reply to message #241523] Tue, 29 May 2007 21:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I'd solve this by using REPLACE & LENGTH functions
Re: Find the spaces in a word [message #241529 is a reply to message #241528] Tue, 29 May 2007 21:56 Go to previous messageGo to next message
mohankumar
Messages: 8
Registered: May 2007
Junior Member
Could you please post the query if possible...
Re: Find the spaces in a word [message #241531 is a reply to message #241523] Tue, 29 May 2007 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Could you please post the query if possible...
You can lead some people to knowledge, but you can't make them think.
How do you expect to learn by simply copying other people's answers?
Do you think your co-workers will do your job for you?
Exactly what are YOU contributing to this effort?
Re: Find the spaces in a word [message #241533 is a reply to message #241531] Tue, 29 May 2007 22:05 Go to previous messageGo to next message
mohankumar
Messages: 8
Registered: May 2007
Junior Member
I am sorry anacedent,

Here I am trying in the following ways.

select substr('enter the values' , Instr('enter the values',' ')+1 ) from dual;
Re: Find the spaces in a word [message #241535 is a reply to message #241523] Tue, 29 May 2007 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
select substr('enter the values' , Instr('enter the values',' ')+1 ) from dual; 

SUBSTR('EN
----------
the values


How do you plan on transforming any string to a number (of spaces)?
Re: Find the spaces in a word [message #241584 is a reply to message #241535] Wed, 30 May 2007 02:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
mohan,
anacedent has given you all clues you need:
LENGTH --> look it up in the Oracle online documentation
REPLACE --> look it up in the Oracle online documentation

If you can't figure it out here is an extra hint: With replace you can create a string without the spaces. If you substract the length of that string from the original string, you get the number you're after.

so, basically it would be something like
SELECT LENGTH(the_original_string)
       -
       LENGTH(<your_replace_expression_on_the_original_string>)
FROM   dual
Some regulars here might call this spoon feeding, but I left some chunks so you'll have to chew.

If you still can't figure it out there's a magic button on this page. It is called "the search button" Wink

MHE
Re: Find the spaces in a word [message #241714 is a reply to message #241584] Wed, 30 May 2007 08:21 Go to previous message
mohankumar
Messages: 8
Registered: May 2007
Junior Member
Thanks Guys,

It's working..

SQL> select LENGTH('mohan kumar pittsburgh')- LENGTH(replace('mohan kumar pittsburgh',' ','')) from
dual;

LENGTH('MOHANKUMARPITTSBURGH')-LENGTH(REPLACE('MOHANKUMARPITTSBURGH','',''))
----------------------------------------------------------------------------
2
Previous Topic: funtion to count characters
Next Topic: Performance Problem
Goto Forum:
  


Current Time: Thu Dec 08 16:10:08 CST 2016

Total time taken to generate the page: 0.09527 seconds