Home » SQL & PL/SQL » SQL & PL/SQL » query
query [message #186523] Tue, 08 August 2006 05:52 Go to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member


how can we write the query


i/p = ***INDIA**ABC

disply count of * from above string;


Prasad
Re: query [message #186525 is a reply to message #186523] Tue, 08 August 2006 06:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL> select length('***INDIA**ABC') - length(replace('***INDIA**ABC','*',''))
  2  from dual;

LENGTH('***INDIA**ABC')-LENGTH(REPLACE('***INDIA**ABC','*',''))
---------------------------------------------------------------
                                                              5
Re: query [message #186526 is a reply to message #186523] Tue, 08 August 2006 06:05 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

No Message Body

[Updated on: Tue, 08 August 2006 06:07]

Report message to a moderator

Re: query [message #186527 is a reply to message #186523] Tue, 08 August 2006 06:07 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Method 1 (requires 10g):
SQL> select length(regexp_replace('***INDIA**ABC','[^*]','')) x
  2  from   dual
  3  /

         X
----------
         5


Method 2:
SQL> select length('***INDIA**ABC')
  2          -
  3         length(replace('***INDIA**ABC','*')) x
  4  from   dual
  5  /

         X
----------
         5


There are more ways...

MHE
Previous Topic: Getting error while compiling the procedure
Next Topic: SELECTING RANDOM NUMBER & INSERTING INTO ANOTHER TABLE ?
Goto Forum:
  


Current Time: Fri Dec 02 12:32:04 CST 2016

Total time taken to generate the page: 0.10227 seconds