|
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? |
|
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;