OraQA

The following SQL pattern can be used to filter and locate the position of non-alphanumeric characters in a table column:

SQL> CREATE TABLE t3 AS
  2  SELECT '*1(2+A^G=d#' AS str FROM dual
  3  UNION ALL
  4  SELECT 'a%b*c^d_e}f' AS str FROM dual;

Table created.

SQL> COLUMN old_str FORMAT A20
SQL> COLUMN New_str FORMAT A20
SQL> COLUMN Bad_char_positions FORMAT A20
SQL> SELECT str old_str,
  2         REGEXP_REPLACE(str,'[^[:alnum:]]','') New_str,
  3         MAX(ltrim(sys_connect_by_path(INSTR(st,'#',1,LEVEL),','),',')) AS
  4           Bad_char_positions
  5  FROM
  6  (SELECT str,
  7          REGEXP_REPLACE(str,'[^[:alnum:]]','#') as st
  8   FROM   t3
  9  )
 10  CONNECT BY PRIOR str = str
 11  AND INSTR (st, '#', 1, LEVEL) > 0
 12  AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
 13  GROUP BY str;

OLD_STR              NEW_STR              BAD_CHAR_POSITIONS
-------------------- -------------------- --------------------
a%b*c^d_e}f          abcdef               2,4,6,8,10
*1(2+A^G=d#          12AGd                1,3,5,7,9,11

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.