Home » SQL & PL/SQL » SQL & PL/SQL » How to get count of character in the table (Oracle10g, Oracle11g)
How to get count of character in the table [message #636915] Tue, 05 May 2015 10:17 Go to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
Hi All,

I'm trying to write a query.

Requirement is like this.

Need to get the total count of one character present in one table.

Below is one sample data.

There is table T1 which has col1, col2 and col3.

Col1 Col2 Col3
# *
* & $
( * *
* % !

Question is i want to get the total of count of character '*' in the table irrespective of number of columns in that table.

From the above sample data i should get the count as '5'

[Updated on: Tue, 05 May 2015 10:18]

Report message to a moderator

Re: How to get count of character in the table [message #636917 is a reply to message #636915] Tue, 05 May 2015 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


We don't do homework assignments.
Re: How to get count of character in the table [message #636920 is a reply to message #636915] Tue, 05 May 2015 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First try to find how to count the * in a string and come back.

Re: How to get count of character in the table [message #636967 is a reply to message #636920] Wed, 06 May 2015 11:44 Go to previous messageGo to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
Anyways thanks for suggestion Michel and Blackswan.

I got the solution.

Below is the solution for the same.

WITH got_columns_123 AS
(SELECT col1 || col2 || col3 AS columns_123
FROM t1)
SELECT NVL (SUM ( LENGTH (columns_123)
- NVL (LENGTH (REPLACE (columns_123, '*')), 0)
),
0
)
FROM got_columns_123;
Re: How to get count of character in the table [message #636968 is a reply to message #636967] Wed, 06 May 2015 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you explain your query?

Re: How to get count of character in the table [message #636977 is a reply to message #636967] Thu, 07 May 2015 01:01 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Another way using regexp_replace to replace everything other than '*' with an empty string. And then take the length of the remaining string:

SQL> WITH DATA AS(
  2  SELECT '#' c1, '*' c2, NULL c3 FROM dual UNION ALL
  3  SELECT '*', '&', '$' FROM DUAL UNION ALL
  4  SELECT '(', '*', '*' FROM DUAL UNION ALL
  5  SELECT '*', '%', '!' FROM DUAL
  6  )
  7  SELECT SUM(LENGTH(regexp_replace(c1||c2||c3, '[^*]', ''))) cnt
  8  FROM DATA;

       CNT
----------
         5

SQL>



Regards,
Lalit
Previous Topic: PL/SQL: ORA-00980: synonym translation is no longer valid
Next Topic: View is not able to execute successfully because of using
Goto Forum:
  


Current Time: Thu Mar 28 20:00:35 CDT 2024