Search code examples
sqloracle-databaseregexp-like

Oracle SQL -Find strings that have a character from ASCII 0 to ASCII 32 and above ASCII 128

There are some entries in my table which have non printing characters. I tried to write a simple script to execute on Oracle DB- 

select column from table where regexp_like(column,'([[:cntrl:]]+)');

But this even matched strings with multiple spaces . But spaces are OK.

What is the best query?

Solution

  • Try using UNISTR with an appropriate hexadecimal range, corresponding to the ASCII range you wish to cover:

    SELECT column
FROM table
WHERE NOT REGEXP_LIKE('a',  '[' || unistr('\0021') || '-' || unistr('\007A') || ']')

    This was the best I could do to get it to work. The character range spans from ASCII character 33 to 122. You may use the negation of this range to target the characters you want.

    Demo