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
Post a Comment