How to get count of character in the table [message #636915] |
Tue, 05 May 2015 10:17 |
|
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 #636977 is a reply to message #636967] |
Thu, 07 May 2015 01:01 |
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
|
|
|