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

Question

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?

Tim Biegeleisen · Accepted Answer · 2018-03-07T07:36:45.137

0

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

edited Mar 07 '18 at 07:36

answered Mar 07 '18 at 06:15

Tim Biegeleisen

502,043
27
286
360

I ried this query - I get a missing parenthesis problem. I tried putting the entire condition in one bracket. But I do not get the required recored – Prateek Narendra Mar 07 '18 at 06:22
@PrateekNarendra Apologies...I can't test my answer right now. Try it again please. – Tim Biegeleisen Mar 07 '18 at 06:27

score 0 · Answer 2 · answered Mar 07 '18 at 07:58

0

What about this:

select column 
from table 
where NOT regexp_like(column,'[ -~]');

answered Mar 07 '18 at 07:58

Wernfried Domscheit

54,457
9
76
110

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

2 Answers2

Demo