Home » SQL & PL/SQL » SQL & PL/SQL » counting
counting [message #294530] Thu, 17 January 2008 23:17 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
How to find no.of 'S' from the name 'sushanthsharma'.

Plz write a query for this...

Thank you
Re: counting [message #294531 is a reply to message #294530] Thu, 17 January 2008 23:21 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
By subtracting two lengths: full string length minus length of a string when 's' has being removed from it:
SQL> SELECT
  2    LENGTH('sushanthsharma') - LENGTH(REPLACE('sushanthsharma', 's', '')) l
  3  FROM dual;

               L
----------------
               3

SQL>
Re: counting [message #294535 is a reply to message #294531] Thu, 17 January 2008 23:26 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Thank u very much...

How to find the same query using "INSTR"

Thank u.
Re: counting [message #294542 is a reply to message #294535] Thu, 17 January 2008 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about telling us what you tried?

Regards
Michel
Re: counting [message #294582 is a reply to message #294530] Fri, 18 January 2008 01:17 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And why do you need a complex query when there exists a simple query as suggested by littlefoot ?

Thumbs Up
Rajuvan.

[Updated on: Fri, 18 January 2008 01:17]

Report message to a moderator

Re: counting [message #294729 is a reply to message #294535] Fri, 18 January 2008 12:59 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you're on 11g (not likely, but who knows), research use of the REGEXP_COUNT function.
Re: counting [message #294731 is a reply to message #294729] Fri, 18 January 2008 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but... How to find the same query using "INSTR". Wink

Regards
Michel

[Updated on: Fri, 18 January 2008 13:29]

Report message to a moderator

Re: counting [message #294737 is a reply to message #294731] Fri, 18 January 2008 14:11 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Let's do SUBSTR, just for fun:
SQL> SELECT SUM(DECODE(SUBSTR('sushanthsharma', LEVEL, 1), 's', 1, 0)) cnt
  2  FROM dual
  3  CONNECT BY LEVEL <= LENGTH('sushanthsharma');

             CNT
----------------
               3

SQL>
Re: counting [message #294739 is a reply to message #294737] Fri, 18 January 2008 14:18 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And, because you asked for it, an INSTR way to do that:
SQL> SELECT COUNT(DISTINCT INSTR('sushanthsharma', 's', LEVEL)) cnt
  2  FROM dual
  3  WHERE INSTR('sushanthsharma', 's', LEVEL) <> 0
  4  CONNECT BY LEVEL <= LENGTH('sushanthsharma');

             CNT
----------------
               3

SQL>
Re: counting [message #294741 is a reply to message #294737] Fri, 18 January 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let's go another puzzle: how many ways to do it without using INSTR? Laughing

Regards
Michel

Edit: Oups! too late.

[Updated on: Fri, 18 January 2008 14:21]

Report message to a moderator

Re: counting [message #294742 is a reply to message #294741] Fri, 18 January 2008 14:27 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Allright!
SQL> SELECT LENGTH(REGEXP_REPLACE(TRANSLATE('sushanthsharma',
  2                                         'abcdefghijklmnopqrstuvwxyz',
  3                                         '                  *       '
  4                                        ), '[[:blank:]]+', ''
  5                              )) cnt
  6  FROM dual;

             CNT
----------------
               3

SQL>
Previous Topic: Using bind variables with a variable length IN clause
Next Topic: how to query max(date) plus tuning
Goto Forum:
  


Current Time: Sun Dec 04 02:28:00 CST 2016

Total time taken to generate the page: 0.17858 seconds