|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 7th January 2007 Oracle version(s): 10.2 |
|
How do I filter and locate the position of non-alphanumeric characters in a SQL Statement? |
|
1) The following SQL pattern can be used to filter and locate the position of Non-alphanumeric characters in a table column .
create table t3 as
select '*1(2+A@G=d#' as str from dual
UNION ALL
select 'a%b*c@d_e}f' as str from dual
SQL> COLUMN old_str FORMAT A20
SQL> COLUMN New_str FORMAT A20
SQL> COLUMN Bad_char_positions FORMAT A20
SELECT str old_str,
REGEXP_REPLACE(str,'[^[:alnum:]]','') New_str,
MAX(ltrim(sys_connect_by_path(INSTR(st,'#',1,LEVEL),','),',')) AS Bad_char_positions
FROM
(SELECT str,
REGEXP_REPLACE(str,'[^[:alnum:]]','#') as st
FROM
t3
)
CONNECT BY PRIOR str = str
AND INSTR (st, '#', 1, LEVEL) > 0
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
GROUP BY STR
OLD_STR NEW_STR BAD_CHAR_POSITIONS
-------------------- --------------------
--------------------
*1(2+A@G=d# 12AGd 1,3,5,7,9,11
a%b*c@d_e}f abcdef 2,4,6,8,10
SQL> spool off;