The Oracle (tm) Users' Co-Operative FAQ

How do I filter and locate the position of non-alphanumeric characters in a SQL Statement?


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?

Back to index of questions


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;


 

Back to top

Back to index of questions