Oracle query to identify columns having special characters

Question

I'm trying to write a SQL query to return rows which has anything other than alphabets, numbers, spaces and following chars '.', '{','[','}',']' Column has alphabets like Ÿ, ¿

eg:- There's a table TEST with 2 columns - EmpNo and SampleText EmpNo is simple sequence and SampleText has values like

('12345abcde','abcdefghij','1234567890','ab c d 1 3','abcd$%1234','%^*&^%$#$%','% % $ #  %','abcd 12}34{','MINNEAŸPOLIS','THAN ¿VV ¿A')

I want to write a query which should eliminate all rows which have even a single special character except .{[}]. In above example, it should return EmpNo - 1,2,3,4 and 8 I tried REGEXP_LIKE but I'm not getting exactly what I need.

Query I used:

SELECT * FROM test 
WHERE REGEXP_LIKE(sampleText, '[^A-Z^a-z^0-9^[^.^{^}]' ,'x'); 

This is not ignoring blanks and I also need to ignore closing bracket ']'

Answer 1

You can use regular expressions for this, so I think this is what you want:

select t.*
from test t
where not regexp_like(sampletext, '.*[^a-zA-Z0-9 .{}\[\]].*')