Home » SQL & PL/SQL » SQL & PL/SQL » How to caluculate particular characters in a string
How to caluculate particular characters in a string [message #246186] Wed, 20 June 2007 04:07 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

How to caluculate particular characters in a string.

Ex: A,B, C, D,

I want to count how many comma's are there in particular string value.

In the above example its 4.

Thanks in advance

Re: How to caluculate particular characters in a string. [message #246189 is a reply to message #246186] Wed, 20 June 2007 04:16 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select length('A,B, C, D,') - length(translate('A,B, C, D,', 'a,', 'a')) from dual 
Re: How to caluculate particular characters in a string [message #246192 is a reply to message #246186] Wed, 20 June 2007 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select length('A,B, C, D,')-length(replace('A,B, C, D,', ',', '')) from dual;
LENGTH('A,B,C,D,')-LENGTH(REPLACE('A,B,C,D,',',',''))
-----------------------------------------------------
                                                    4

1 row selected.

Regards
Michel
Re: How to caluculate particular characters in a string [message #246456 is a reply to message #246186] Thu, 21 June 2007 01:02 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10g, you can consider:

SQL> select length(regexp_replace('A,B, C, D,','[^,]')) from dual;

LENGTH(REGEXP_REPLACE('A,B,C,D,','[^,]'))
-----------------------------------------
                                        4
Previous Topic: HOW DO I LIST A STORED PROCEDURE FROM SQL PLUS???
Next Topic: oracle data block
Goto Forum:
  


Current Time: Sun Dec 04 08:54:07 CST 2016

Total time taken to generate the page: 0.18264 seconds