I am testing a varchar2 that is about 1000 bytes long. I am trying to check for any control characters(characters that would not be able to be displayed on the screen). Any ideas?
Printable View
I am testing a varchar2 that is about 1000 bytes long. I am trying to check for any control characters(characters that would not be able to be displayed on the screen). Any ideas?
If by control character you mean characters with ASCII codes between 0 and 31 then the following query will find all records where col1 contains any of such characters:
SELECT * FROM my_tab
WHERE INSTR (TRANSLATE
(
col1,
CHR(1)||CHR(2)||...||CHR(30)||CHR(31),
CHR(0)||CHR(0)||...||CHR(0)||CHR(0)
)
CHR(0)) > 0);
This efectually translates all characters below ASCII code 32 to a character ASCII 0 by using TRANSLATE and then searches for any occurence of such character with ASCII code 0 by using INSTR.