The Oracle (tm) Users' Co-Operative FAQ

How do I find all the starting positions and counts of consecutive occurrences of non-alphanumeric characters (at each position) in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 5th February 2007

Oracle version(s): 10.2

How do I find all the starting positions and counts of consecutive occurrences of non-alphanumeric character in a SQL Statement?

Back to index of questions


The following SQL pattern can be used to find all the starting positions and count of consecutive occurrences of non-alphanumeric  characters (at each position)  in a SQL Statement?

 

create table t6 as SELECT'**ABCD****EE**WXYZ*****KK**' as str FROM dual

 

 

SELECT str, min(num) char_start, max(num) - min(num) +1 as num_of_char

FROM

(SELECT str, max(grp) OVER (ORDER BY num) grp, num

   FROM (SELECT str , num ,

               CASE WHEN nvl(lag(num) OVER (ORDER BY num),num) != num-1

                    THEN num

                     END grp

          FROM

          (SELECT str, INSTR(new_str,chr(0), 1, LEVEL) AS num

             FROM

             (SELECT str, REGEXP_REPLACE(str, '[^[:alnum:]]' , chr(0) )      

                          as new_str

                FROM t6

             )

           CONNECT BY PRIOR str = str

           AND INSTR (new_str, chr(0) , 1, LEVEL) > 0

           AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

          )

        )

)

GROUP BY  str, grp;

 

 

STR                         CHAR_START   NUM_OF_CHAR

--------------------------- ----------   -----------

**ABCD****EE**WXYZ*****KK**          1           2

**ABCD****EE**WXYZ*****KK**          7           4

**ABCD****EE**WXYZ*****KK**         13           2

**ABCD****EE**WXYZ*****KK**         19           5

**ABCD****EE**WXYZ*****KK**         26           2

 

 

SQL> spool off;


 

Back to top

Back to index of questions