Tuesday, January 10, 2012

Count occurance of character in a string

To count the number of times a particular character occurs in a string, use the below SQL query.
If you are using Oracle 11, you can use the new REGEXP_COUNT - note that it uses regular expressions and so special character need to be escaped:

SELECT  REGEXP_COUNT (first_name, '\.') 
AS dot_count 
FROM employee;

If you are using any other version of Oracle, then use this query:

SELECT  first_name, 
LENGTH (first_name) - LENGTH (REPLACE (first_name, '.')) 
AS dot_count 
FROM employee;

No comments:

LinkWithin

Related Posts Plugin for WordPress, Blogger...