-- New For Oracle 11g - REGEXP_COUNT
-- In the example below, the "E" and "e" are counted starting from
-- position 1 and 'c' represents string/character count to be case
-- sensitive

WITH q_t AS
(SELECT 'Enhancements To Oracle In 11g' title  FROM DUAL UNION
 SELECT 'New Feature In Oracle 11g' title      FROM DUAL
 )
SELECT 
 title,
 REGEXP_COUNT(title,'E', 1, 'c') "Capital_E",
 REGEXP_COUNT(title,'e', 1, 'c') "Small_e"
FROM q_t;



WITH q_t AS
(SELECT '123,"10-JAN-14",25551.75,165.00,SOLD,C' ref_data  
 FROM DUAL)
SELECT 
 ref_data,
 REGEXP_COUNT(ref_data,',') commas,
 REGEXP_COUNT(ref_data,'-') "Dash -"
FROM q_t;



-- Using REGEXP_COUNT, comma delimited string can be split into indexed 
-- string elements (CAST is used for display format).

WITH q_tab AS
(SELECT 'AAA,BBB,CCC,DDD,EEE' str_txt  FROM DUAL)
SELECT  level str_idx,
 CAST(REGEXP_SUBSTR(str_txt,'[^,]+',1,level)
     AS VARCHAR2(10)) str_val
FROM  q_tab
CONNECT BY level <= REGEXP_COUNT(str_txt,',')+1;



WITH q_tab AS
(SELECT  'http://www.kayshav.com/' str_val FROM DUAL UNION
 SELECT  '\\main\images\' str_val FROM DUAL)
SELECT  DECODE(REGEXP_COUNT(str_val,'\\\\[a-zA-Z]+\\'),0,'',
               'Folder -> '||str_val) folder_str,
        DECODE(REGEXP_COUNT(str_val,'//'),0,'',
               'URL -> '||str_val) url_str
FROM  q_tab
WHERE  REGEXP_LIKE(str_val,'\\\\[a-zA-Z]+\\')  OR
       REGEXP_LIKE(str_val,'//');