oracle - Simplifying repetitive function call in SQL -


i'm looking way simplify following query in oracle 10g, preferably without resorting pl/sql constructs:

select * my_table instr(my_col, '<')     + instr(my_col, '>')     + instr(my_col, ':')     + instr(my_col, '"')     + instr(my_col, '/')     + instr(my_col, '\')     + instr(my_col, '|')     + instr(my_col, '?')     + instr(my_col, '*')     + instr(my_col, chr(0))     + instr(my_col, chr(1))     + instr(my_col, chr(2))        -- ...     + instr(my_col, chr(31)) > 0  

the query looking characters illegal in windows paths. thankfully, later appending known legal characters my_col, don't have handle other more complicated cases of illegal names. can assumed my_col not null.

the query works purposes, unhappy copy-paste programming i've done.

use regular expression:

where regexp_like(my_col, '[<>:"''/\\|?*\x00\x01\x02]')  

or, might prefer list characters are valid. this:

where regexp_like(my_col, '[^a-za-z0-9_]') 

Comments