How to find all the starting positions and counts of consecutive occurrences of non-alphanumeric characters in a SQL statement
February 26th, 2007 By Frank Zhou
The following SQL pattern can be used to find all the starting positions and counts of consecutive occurrences of non-alphanumeric characters (at each position) in a SQL statement.
SQL> CREATE TABLE t6 AS
2 SELECT'**ABCD****EE**WXYZ*****KK**' AS str
3 FROM DUAL
4 /
Table created.
SQL> SELECT str,
2 MIN (num) char_start,
3 MAX (num) - MIN (num) + 1 AS num_of_char
4 FROM (SELECT str,
5 MAX (grp) OVER (ORDER BY num) grp,
6 num
7 FROM (SELECT str,
8 num,
9 CASE
10 WHEN NVL (LAG (num) OVER (ORDER BY num), num) !=
11 num - 1
12 THEN num
13 END grp
14 FROM (SELECT str,
15 INSTR (new_str, CHR (0), 1, LEVEL)
16 AS num
17 FROM (SELECT str,
18 REGEXP_REPLACE (str,
19 '[^[:alnum:]]',
20 CHR (0)
21 ) AS new_str
22 FROM t6)
23 CONNECT BY PRIOR str = str
24 AND INSTR (new_str, CHR (0), 1, LEVEL) > 0
25 AND PRIOR DBMS_RANDOM.STRING ('p', 10) IS NOT NULL)))
26 GROUP BY str, grp;
STR CHAR_START NUM_OF_CHAR
--------------------------- ---------- -----------
**ABCD****EE**WXYZ*****KK** 1 2
**ABCD****EE**WXYZ*****KK** 26 2
**ABCD****EE**WXYZ*****KK** 13 2
**ABCD****EE**WXYZ*****KK** 7 4
**ABCD****EE**WXYZ*****KK** 19 5